regexp_extract

select regexp_extract('你好，李先生你的电话是15622150839','电话是([0-9])+', 0)

输出

电话是15622150839

regexp_replace

第二个参数，注意转义
Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\s' is necessary to match whitespace, etc.

示例1

select
  regexp_replace(poi, '\\n', '')
from (
  select '
  2015' poi
  union
  select '
  2016' poi
) t1

输出

2015
2016

示例2

select
  regexp_replace(poi, '\\s', '')
from (
  select '2015 ab' poi
  union
  select '2016  77' poi
) t1

输出

2015ab
201677

regexp

示例

select 
    word,
    word regexp('aa|bb|cc') reg
FROM (
    SELECT "aaA" word
    UNION
    SELECT "bbB" word
    UNION
    SELECT "ccC" word
    UNION
    SELECT "xyz" word
) t1

输出

word reg
aaA true
bbB true
ccC true
xyz false

case when then 条件添加 and or

case when (t.batchid  <>' ' and t.batchid is not null) then 1 else 0 end