CODEWITHSUNDEEP
sqloracle-database
Arun Palanisamy
Arun Palanisamy

Reputation: 5469

How to get the text between two different special Characters in oracle?

I'm new to oracle. I came across a problem like i need to get the text between two different special characters. For eg.

txt='$Name:micheal$dept:$sal:$dob:1.0$place:india$'

I need to get the Name and place. 

SELECT SUBSTR(txt, 1 ,INSTR(txt, '$', 1, 1)-1) FROM dual;
---------------------------------------------------------
Name:micheal

I'm getting above one. How can i get value between ':' and '$' only micheal? Help me in this. Also i need to get india.

Upvotes: 0

Views: 3947

Answers (1)

Multisync
Multisync

Reputation: 8797

WITH t AS (
  SELECT 1 ID, '$Name:micheal$dept:$sal:$dob:1.0$place:india$' s FROM dual
  /*
  union all
  SELECT 2 ID, '$Name:micheal$dept:$sal:$dob:1.0$place:india$' s FROM dual
  */
)
SELECT ID, regexp_substr(token, '[^:]+', 1, 1) name_
         , regexp_substr(token, '[^:]+', 1, 2) value_
FROM (
  SELECT id, regexp_substr(s, '[^\$]+', 1, lvl) token, lvl
  FROM t JOIN  
  (
    SELECT LEVEL lvl FROM dual 
    CONNECT BY LEVEL < (SELECT MAX(LENGTH(s) - LENGTH(REPLACE(s, '$'))) FROM t)
  ) x ON LENGTH(s) - LENGTH(REPLACE(s, '$')) > lvl
)
order by id, lvl;

1) Calculate the number of tokens divided by $: LENGTH(s) - LENGTH(REPLACE(s, '$')

2) I wrote the variant for a table (suppose there are several rows to parse)

3) CONNECT BY LEVEL < max - generate numbers from 1 to max number of $ possible in the table

4) Join number with the table to parse each token using regexp_substr(, , , occurence)

5) Parse again to split name/value

To parse just one string:

SELECT regexp_substr(token, '[^:]+', 1, 1) name_,
       regexp_substr(token, '[^:]+', 1, 2) value_
FROM (
  SELECT regexp_substr(s, '[^\$]+', 1, lvl) token, lvl
  FROM (
    SELECT s, LEVEL lvl FROM (SELECT '$Name:micheal$dept:$sal:$dob:1.0$place:india$' s FROM dual) 
    CONNECT BY LEVEL < LENGTH(s) - LENGTH(REPLACE(s, '$'))
  )
);

Upvotes: 1

Related Questions