使用REGEXP_SUBSTR按竖线分隔提取字段时结果异常的解决方法

问题场景 #

要从竖线|分隔的打包字段里按位置提取指定值，字段可能包含空值（比如ONE|TWO||||SIX||EIGHT||TEN|ELEVEN_THE_BAND|TWELVE），之前用的正则提取时出现异常——比如指定位置2，结果返回|FOUR|，完全不符合预期。

可行拆分方法 #

提取除最后一个外的任意位置值 #

对于总字段数里前N-1个位置的内容，用匹配「内容+竖线」的正则，能精准定位每个位置的内容：

REGEXP_SUBSTR(the_column, '([[:alpha:]_]*)(\|)', 1, n)

• 这里的([[:alpha:]_]*)用来匹配字母、下划线组成的内容，如果需要支持数字，直接改成([[:alnum:]_]*)就行；
• 参数n是你要提取的位置序号，比如提第2个值就设n=2，不过返回结果会带末尾的竖线，最后用REPLACE去掉就行：REPLACE(REGEXP_SUBSTR(the_column, '([[:alpha:]_]*)(\|)', 1, n), '|', '')。

提取最后一个值 #

最后一个值后面没有竖线，得换个匹配「竖线+内容」的正则：

REPLACE(REGEXP_SUBSTR(the_column, '(\|)([[:alpha:]_]*)', 1, 1), '|', '')

这个正则会匹配最后一个值前面的竖线和值本身，最后用REPLACE把前置的竖线删掉就能得到纯内容。

实际测试示例 #

拿字段值ONE|TWO||||SIX||EIGHT||TEN|ELEVEN_THE_BAND|TWELVE测试：

• 提第3个值（空值）：REPLACE(REGEXP_SUBSTR(the_column, '([[:alpha:]_]*)(\|)', 1, 3), '|', '') 返回空字符串；
• 提第6个值：REPLACE(REGEXP_SUBSTR(the_column, '([[:alpha:]_]*)(\|)', 1, 6), '|', '') 返回SIX；
• 提最后一个值：REPLACE(REGEXP_SUBSTR(the_column, '(\|)([[:alpha:]_]*)', 1, 1), '|', '') 返回TWELVE。

内容的提问来源于stack exchange，提问作者mojave