PLSQL Getting values from string

Question

I have this varchar2(2000) string:

id=100\nid2=0\nid3=0\dtext='more Text'

and I want to get only the values e.g. more Text or 0 (id3).

I was trying to use a customized SPLIT function, where separator is \n but this only returns me for example id3=0 (in this case I need '0' as result).

How can I do this more efficient?

Lalit Kumar B · Accepted Answer · 2015-01-27 11:01:37Z

2

and I want to get only the values e.g. more Text.

Simply use SUBSTR and INSTR

SQL> WITH DATA AS
  2    ( SELECT q'[id=100\nid2=0\nid3=0\dtext='more Text']' str FROM dual
  3    )
  4  SELECT SUBSTR(str,instr(str, '''')+1,LENGTH(SUBSTR(str,instr(str, '''')))-2) str
  5  FROM DATA
  6  /

STR
---------
more Text

SQL>

answered Jan 27, 2015 at 11:01

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

frgtv10 Over a year ago

Your solution means, I have to count for each occurence to get the right content, right?

frgtv10 Over a year ago

But how to get the content for id3 for example?

Lalit Kumar B Over a year ago

In my solution, I considered the text between the single quotes.

Lalit Kumar B Over a year ago

Do you mean you need all the values immediately after = sign?

pablomatico · Accepted Answer · 2015-01-27 12:45:34Z

1

You could get all the values with something like this:

WITH DATA AS
  (SELECT q'[id=100\nid2=0\nid3=0\ndtext='more Text']' str FROM dual)      
SELECT replace(substr(regexp_substr(str,'(=.+?\n)|(=.+?$)',1,level),2),'\n') v    
FROM DATA
CONNECT BY LEVEL <= LENGTH(regexp_replace(str,'([^=]+=.+?\n)|([^=]=.+?$)'))

answered Jan 27, 2015 at 12:45

pablomatico

2,24221 silver badges25 bronze badges

1 Comment

frgtv10 Over a year ago

Wow, thats amazing. I need to have a look for those RegEx. How to get rid of the quotes e.g. 'more Text'

Collectives™ on Stack Overflow

PLSQL Getting values from string

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related