Clever Geek Handbook

Split string using pl / sql using a null connection layer

I am using this following code in Oracle pl / sql (Version: Oracle Database 11g Release 11.2.0.1.0)

select regexp_substr('A~B~C','[^~]+',1,level) output
from dual
connect by level <= length(regexp_replace('A~B~C','[^~]+')) + 1

which gives the following results:

row1: A
row2: B
row3: C

This is perfect, but I have to give a null value, that is:

select regexp_substr('~B~C','[^~]+',1,level) output
from dual
connect by level <= length(regexp_replace('~B~C','[^~]+')) + 1

I expected and wanted the following:

row1: <null>
row2: B
row3: C

but got this output:

row1: B
row2: C
row3: null

Am I doing pl / sql code wrong? How can I get it to work correctly?

+3


source to share


5 answers


Because I used single characters as an example in my question, but as a practical matter I use long strings in my project, like "How ~ do ~ I ~ do ~ this"

I found this solution from OTN Oracle.com , thanks to chris227.

SELECT CAST(REGEXP_SUBSTR (str, '(.*?)(~|$)', 1, level, null, 1) AS CHAR(12))  output
FROM (select 'How~do~I~do~this' as str from dual)
CONNECT BY level <= regexp_count(str, '~') + 1;


This will work even with single characters.

Hope this helps others looking for similair solutions.

+2


source


you can concatenate INSTR

und SUBSTR

to achieve your desired result:



select  
  str, 
  replace(substr(str, 
                 case level 
                 when 1 then 0 
                 else instr( str, '~',1, level-1) 
                 end 
                  +1,
                 1
                ), '~')
from ( select 'A~B~C~D~E' as str from dual)
connect by level <= length(regexp_replace(str,'[^~]+')) + 1
;
+4


source


You can use NULLS FIRST

in a sentenceORDER BY 

select regexp_substr('~B~C','[^~]+',1,level) output
from dual
connect by level <= length(regexp_replace('~B~C','[^~]+')) + 1
ORDER BY regexp_substr('~B~C','[^~]+',1,level) NULLS FIRST;

For a quote from Oracle documentation

If zero order is not specified, then processing of zero value:

NULLS LAST if the sort is ASC

NULLS FIRST if the sort is DESC

If neither ascending nor descending order is specified, and zero order is also not specified, both defaults are used, and thus the order will be ascended with NULLS LAST.

+2


source


I know it's getting late, but I think what you need (and I do):

select REPLACE(regexp_substr('A~~C','[^~]*(~)?',1,level),'~') output, level
from dual
connect by level <= length(regexp_replace('A~~C','[^~]+')) + 1
ORDER BY level;
0


source


Since this issue was fresh in my opinion and I happened to see this post, I respectfully submit my suggestion, which builds on the answer by user3767503. This reduces the number of function calls required. It uses some 11g updates for regexp_substr()

and uses regexp_count()

, which I believe was also introduced in 11g. The number of fields is assumed to be the number of separators plus one.

select regexp_substr('AAA~X~C~~DD~~~E', '([^~]*)(~|$)', 1, level, null, 1) output, level
from dual
connect by level <= regexp_count('AAA~X~C~~DD~~~E','~') + 1 
ORDER BY level;

See this post for more information and details on reading a regex pattern: Split Partial Values ​​in Columns .

Bottom Line is a commonly used regex pattern '[^<delimiter>]+'

to parse a string if there is zero in the list and should be avoided IMHO.

0


source






More articles:


All Articles