Разделить(split) строку по заданному символу
Данный пример 'А.Б.В.Г.Д'(&1) строку разделяет символом '.' (точка)(&2) .
WITH
OVALS AS ( SELECT '&1' bval ,'&2' psymbol FROM DUAL),
OFILTERED AS (
SELECT REGEXP_SUBSTR(BVAL, '[^'||psymbol||']+', 1, LEVEL) BVAL,
LEVEL LV,
LAG(LEVEL, 1, 0) OVER (PARTITION BY bval ORDER BY LEVEL) LG,
psymbol
psymbol
FROM OVALS CONNECT BY REGEXP_SUBSTR(BVAL, '[^'||psymbol||']+', 1, LEVEL) IS NOT Null)
SELECT bval FROM ( SELECT REPLACE(BVAL,psymbol,'') BVAL FROM OFILTERED WHERE LV != LG);
WITH
SELECT bval FROM ( SELECT REPLACE(BVAL,psymbol,'') BVAL FROM OFILTERED WHERE LV != LG);
или
WITH
OVALS AS ( SELECT '&1' bval ,'&2' psymbol FROM DUAL),
OFILTERED AS (
SELECT REGEXP_SUBSTR(BVAL, '[^'||psymbol||']+', 1, LEVEL) BVAL
FROM OVALS CONNECT BY REGEXP_SUBSTR(BVAL, '[^'||psymbol||']+', 1, LEVEL) IS NOT Null)
SELECT bval FROM OFILTERED ;
SELECT bval FROM OFILTERED ;
или без оператора WITH
SELECT bval FROM ( SELECT REPLACE(BVAL,psymbol,'') BVAL FROM
( SELECT REGEXP_SUBSTR(BVAL, '[^'||psymbol||']+', 1, LEVEL) BVAL, LEVEL LV,
LAG(LEVEL, 1, 0) OVER (PARTITION BY bval ORDER BY LEVEL) LG, psymbol
FROM ( SELECT '&1' bval ,'&2' psymbol FROM DUAL) OVALS
CONNECT BY REGEXP_SUBSTR(BVAL, '[^'||psymbol||']+', 1, LEVEL) IS NOT Null) OFILTERED
WHERE LV != LG);
LAG(LEVEL, 1, 0) OVER (PARTITION BY bval ORDER BY LEVEL) LG, psymbol
FROM ( SELECT '&1' bval ,'&2' psymbol FROM DUAL) OVALS
CONNECT BY REGEXP_SUBSTR(BVAL, '[^'||psymbol||']+', 1, LEVEL) IS NOT Null) OFILTERED
WHERE LV != LG);
|
BVAL |
|
А |
|
Б |
|
В |
|
Г |
|
Д |
