在Oracle DB中分割字符串 [英] Split string in Oracle DB

问题描述

在示例中，我有3行的表:

I have table with 3 rows in example:

|-Id-||--props--|
1        aa = black
2        aa = change
         d = eexplore
3        xxx = yield
         d = int
         mmmm = no

我想用结果写SQL语句:

I want to write SQL statement with results:

aa    
d
xxx
mmm

即输出成对x = y的所有第一个元素.给定表的每一行中的对都可以用newline分隔.

I.e. output all first elements in pairs x=y. The pairs in each row of given table could be separated by newline.

我尝试了以下SQL，但结果不正确

I've tried the following SQL but the results are doesn't correct

    SELECT REGEXP_SUBSTR ('a = b, b = c',
                          '[^=]',
                          1,
                          LEVEL) as name
      FROM DUAL
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT ('a = b, b = c', '=') FROM DUAL)

推荐答案

请尝试以下操作，sql小提琴

Kindly try the below,the sql fiddle here

with names as ( SELECT REGEXP_SUBSTR (regexp_replace('aa = b, bb = c , eee = d','[[:space:]]*',''), '[^=]+', 1, level ) as name FROM DUAL CONNECT BY LEVEL <= (SELECT REGEXP_COUNT ('a = b, b = c , e = d ', '=') FROM DUAL)) select nvl(regexp_substr(name,'[^,]+',1,2),regexp_substr(name,'[^,]+',1,1) ) from names;

with names as ( SELECT REGEXP_SUBSTR (regexp_replace('aa = b, bb = c , eee = d','[[:space:]]*',''), '[^=]+', 1, level ) as name FROM DUAL CONNECT BY LEVEL <= (SELECT REGEXP_COUNT ('a = b, b = c , e = d ', '=') FROM DUAL)) select nvl(regexp_substr(name,'[^,]+',1,2),regexp_substr(name,'[^,]+',1,1) ) from names;

Update1:​​:-您可以用逗号替换新行

Update1:-You can replace the new line with comma

在Unix上，LINEFEED(chr(10))是行尾"标记. 在Windows上，CARRIAGE RETURN/LINEFEED(chr(13)|| chr(10))应该是行标记的结尾

On Unix, LINEFEED (chr(10)) is the "end of line" marker. On windows CARRIAGE RETURN/LINEFEED (chr(13)||chr(10)) should be the end of line marker

with names as
(
 SELECT REGEXP_SUBSTR (regexp_replace(replace('aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'mmmm=no',chr(13)||chr(10),','),'[[:space:]]*',''),
                          '[^=]+',
                          1,
                         level ) as name
      FROM DUAL
CONNECT BY LEVEL <= (SELECT REGEXP_COUNT 
('aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'mmmm=no', '=')
 FROM DUAL)

)
select nvl(regexp_substr(name,'[^,]+',1,2),
regexp_substr(name,'[^,]+',1) ) from names;

这篇关于在Oracle DB中分割字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！