修剪/截断 bigquery 中数据值的最小字母

Question

目前这是我在谷歌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

但是，我没有得到所有值，因为某些"Serial_Number"值以"A"或"B"结尾，而"串行"值则没有。我该怎么做才能在 5 个字符后修剪每个 serial# 的值或截断值以删除最后一个字符。

Answer 1

这行得通吗？

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+')