我需要使用BigQuery提取匹配字符右侧的所有字符

Question

我想使用BigQuery提取位于匹配字符右侧的所有字符。例如:假设我想提取'-'后面的所有字符然后:

1. happy-holiday will give holiday
2. 一去不返

有人能帮我一下吗

Answer 1

Try SPLIT:

SELECT SPLIT("happy-holiday will give holiday", "-")[OFFSET(1)]

或REGEXP_EXTRACT:

SELECT REGEXP_EXTRACT("happy-holiday will give holiday", r"-(.*)")

Answer 2

Sergey提到的SPLIT工作得很好。或者你也可以用老派的方法:)

WITH sample_data AS (
SELECT 'happy-holiday' as text
UNION ALL
SELECT 'gogoa-gone' as text
UNION ALL
SELECT 'hello-world' as text
UNION ALL
SELECT 'i-am-back' as text
UNION ALL
SELECT 'hey-whatever is after the dash' as text
)

SELECT text
, instr(text, '-', -1) as dash_position
, substr(text, instr(text, '-', -1)+1, length(text)) as substr
FROM sample_data

结果:

TEXT                            DASH_POSITION   SUBSTR
---------------------------------------------------------------------------
happy-holiday                   6               holiday
gogoa-gone                      6               gone
hello-world                     6               world
i-am-back                       5               back
hey-whatever is after the dash  4               whatever is after the dash