我在 BigQuery 中有一个表，其中包含一个带有如下文本字符串的变量：
row path
1 /998777/kkjs/lksjaflkjsdf/sdfñklñ{sdf
2 /ñljasdfkgbsdf/5854654/lsjflkjsdf/asdfasdfsdf
3 /11544/sdfsdf/asdfsdfasdfdsf/sssfdsfdsdf
如何查询此表以提取变量的数字，以便获得一个新变量：
row path2
1 998777
2 5854654
3 11544
谢谢！
下面是 BigQuery 标准 SQL 的示例
#standardSQL
WITH `project.dataset.table` AS (
SELECT '/998777/kkjs/lksjaflkjsdf/sdfñklñ{sdf' path UNION ALL
SELECT '/ñljasdfkgbsdf/5854654/lsjflkjsdf/asdfasdfsdf' UNION ALL
SELECT '/11544/sdfsdf/asdfsdfasdfdsf/sssfdsfdsdf'
)
SELECT path, REGEXP_EXTRACT(path, r'/(\d+)/') path2
FROM `project.dataset.table`
结果
Row path path2
1 /998777/kkjs/lksjaflkjsdf/sdfñklñ{sdf 998777
2 /ñljasdfkgbsdf/5854654/lsjflkjsdf/asdfasdfsdf 5854654
3 /11544/sdfsdf/asdfsdfasdfdsf/sssfdsfdsdf 11544
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句