Oracle REGEXP_SUBSTR 一列分割成多列
REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is useful if you need the contents of a match string but not its position in the source string. The function returns the string as VARCHAR2 or CLOB data in the same character set as source_char.
REGEXP_SUBSTR扩展了SUBSTR函数的功能,让你搜索一个字符串的正则表达式模式。它也类似于REGEXP_INSTR,但是它不是返回子串的位置,而是返回子串本身。如果你需要一个匹配字符串的内容,但不需要它在源字符串中的位置,这个函数很有用。该函数将字符串作为VARCHAR2或CLOB数据返回,其字符集与source_char相同
示例
select '/刘/轶/鹤/' SLASH_COLUM
,REGEXP_SUBSTR('/刘/轶/鹤/', '[^/]+', 1, 1) SLASH_COLUM_A
,REGEXP_SUBSTR('/刘/轶/鹤/', '[^/]+', 1, 2) SLASH_COLUM_B
,REGEXP_SUBSTR('/刘/轶/鹤/', '[^/]+', 1, 3) SLASH_COLUM_C
,REGEXP_SUBSTR('/刘/轶/鹤/', '[^/]+', 1, 4) SLASH_COLUM_D
,REGEXP_SUBSTR('/刘/轶/鹤/', '[^/]+', 1, 5) SLASH_COLUM_E
,REGEXP_SUBSTR('/刘/轶/鹤/', '[^/]+', 1, 6) SLASH_COLUM_F
FROM DUAL
;
执行结果
-- 刘轶鹤