使用googleSQL从字符串中只返回1-10000个数字

Question

这是的场景

表A，列Elem1

Elem1
John1235
kkajdak587
klajdsfds5
sjdfsj35
sdkfjsdj57
skjlfjd1
sdjflsj34
lfkjgk108
ksdjlkdjfk288
ksjsfsj1688

我想从列元素1的字符串值中只提取数字。数字从1到10000。

我该怎么做？

Answer 1

BigQuery支持REGEXP_REPLACE来替换而不是[0-9]的任何内容；这将为您提取所有数字。

SELECT 
Elem1, 
REGEXP_REPLACE(Elem1, "[^0-9]", "") as numbersOnly
FROM myTable

如果要将其筛选为1-10000之间的值，则需要将其放入WHERE子句中。

SELECT 
Elem1,
cast(REGEXP_REPLACE(Elem1, "[^0-9]", "") as int64) as numbersOnly
FROM myBaseTable 
WHERE cast(REGEXP_REPLACE(Elem1, "[^0-9]", "") as int64) BETWEEN 1 and 10000