Trimming/ Truncating the lest letter of datavalues in bigquery

Question

Currently this is my code in google BigQuery

SELECT *
FROM
(
    SELECT
        CAST(Serial AS integer) AS Serial,
        Latest_Use,
        Total_Hours,
        Devices_Connected
    FROM [dataworks-356fa:FirebaseArchive.PT_Results]
) AS Model_Results
JOIN
(
    SELECT
       CAST(Serial_Number AS integer) AS Serial_Number,
       Status,
       Model_Number
    FROM [dataworks-356fa:FirebaseArchive.Master_List_PT]
) AS Master_List
    ON Model_Results.Serial = Master_List.Serial_Number

However I am not getting all of the values because some of the "Serial_Number" values end with either an 'A' or 'B', while the "serial" values do not. What do I do to either trim the values of each serial# after 5 characters or truncate the values to take out the last character.

Answer 1

Does this work?

SELECT *
FROM
(
    SELECT
        CAST(Serial AS integer) AS Serial,
        Latest_Use,
        Total_Hours,
        Devices_Connected
    FROM [dataworks-356fa:FirebaseArchive.PT_Results]
) AS Model_Results
JOIN
(
    SELECT
       CAST(Serial_Number AS integer) AS Serial_Number,
       Status,
       Model_Number
    FROM [dataworks-356fa:FirebaseArchive.Master_List_PT]
) AS Master_List
    ON Model_Results.Serial = REGEXP_EXTRACT(Master_List.Serial_Number, r'\d+')