You Asked

SELECT REGEXP_SUBSTR(val_PC, '[^, ]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(val_PC, '[^, ]+', 1, LEVEL) IS NOT NULL

and Connor said...

Here you go

SELECT SUBSTR(val_PC, '[^, ]+', 1, LEVEL) FROM DUAL CONNECT BY SUBSTR(val_PC, '[^, ]+', 1, LEVEL) IS NOT NULL

:-)

But seriously... if you couldn't even take the time to give us even a single execution of your query with a string value. I mean - that suggests you don't value our time much doesn't it ?

"Hey I could do a test case showing what I want, or even just explaining what I want, but who cares, the AskTOM guys can waste their time trying to work it out"

Because obviously you want to parse a csv string into rows. If you had come to AskTOM, and typed in "parse csv string rows" into the search box, the *second* answer would have been:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=separating-comma-separated-words

which has *exactly* the solution you are after, plus numerous others, plus a discussion of the pros and cons of each.

Think of how much *more* benefit you would have got from doing that.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Please provide me some input

neha, November 07, 2017 - 3:05 pm UTC

I need the result to have:
('A','B','C') in Level. I need to use the level structure as REGEXP_SUBSTR does.

The query that you gave me gives error that too many argument in a function. I am confused how to use substr and instr to get the same result .

Sorry I pasted the query like that. I will take care in future.

((pc.DISP_NME in(SELECT SUBSTR(val_PC, '[^, ]+', 1, LEVEL) FROM DUAL CONNECT BY SUBSTR(val_PC, '[^, ]+', 1, LEVEL) IS NOT NULL) and val_PC is not null)
or pl.ASSET_ID_CPC in( SELECT SUBSTR(val_siteNum, '[^, ]+', 1, LEVEL) FROM DUAL CONNECT BY SUBSTR(val_siteNum, '[^, ]+', 1, LEVEL) IS NOT NULL ) and val_siteNum is not null)

November 08, 2017 - 3:40 pm UTC

That SQL fragment still doesn't really help us...

Or explain why you need to use substr/instr

MORE information

Neha Dhingra, November 08, 2017 - 3:47 pm UTC

The result with my query is SELECT SUBSTR('V6A0E5,V6A4H2,V6A0C7,V6A4E1,V6A0A5', INSTR('V6A0E5,V6A4H2,V6A0C7,V6A4E1,V6A0A5', ' [^,]+ ', 1, LEVEL)+1)from dual
CONNECT BY LEVEL <= INSTR('V6A0E5,V6A4H2,V6A0C7,V6A4E1,V6A0A5',' [^,]+')+1;

OUTPUT
V6A0E5,V6A4H2,V6A0C7,V6A4E1,V6A0A5

BUT I NEED output to be
V6A 0E5
V6A 4H2
V6A 0C7
V6A 4E1
V6A 0A5

which is by using
select regexp_substr('V6A 0E5,V6A 4H2,V6A 0C7,V6A 4E1,V6A 0A5','[^,]+',1,level)
from dual
connect by level <= length(regexp_replace('V6A 0E5,V6A 4H2,V6A 0C7,V6A 4E1,V6A 0A5','[^,]'))+1;

THE issue with the query is its taking 2719283 time because its used with a big query and inside WITH clause .

November 09, 2017 - 1:34 am UTC

Please .... check the link we sent

https://asktom.oracle.com/pls/asktom/asktom.search?tag=separating-comma-separated-words

That is what *I* did....

a) clicked on the link
b) read down through the content, picking up lots of useful knowledge
c) found an entry that actually said almost EXACTLY what your question was asking:

"SQL Tokenizer + connect by, ...Version using regexp_substr"

followed by

"Version using plain substr (less elagant but more performant)"

So someone had REGEXP version which they converted to just SUBSTR/INSTR....

SQL> select substr (x,
  2  instr (x, ',', 1, level ) + 1,
  3  instr (x, ',', 1, level+1) - instr (x, ',', 1, level) -1 )
  4  as token
  5  from (select ','||'V6A 0E5,V6A 4H2,V6A 0C7,V6A 4E1,V6A 0A5'||',' as x from dual)
  6  connect by instr (x, ',', 1, level+1) > 0
  7  ;

TOKEN
----------------------------------------------------------------------------------------
V6A 0E5
V6A 4H2
V6A 0C7
V6A 4E1
V6A 0A5

My advice to anyone - the Cut/Paste mind-set without endeavouring to understand isn't the path to being a great developer.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Please provide me some input

MORE information

More to Explore

PL/SQL demos

PL/SQL docs

I need to replace this query with substr and instr

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Please provide me some input

MORE information

More to Explore

PL/SQL demos

PL/SQL docs