asked on

how to solve this query?

Hi expert,
below is my query

SELECT *
  FROM (SELECT REGEXP_REPLACE(
                   REGEXP_SUBSTR(
                       :JSONdata,
                       '"[0-9]+".+?}}',
                       1,
                       LEVEL
                   ),
                   '^[{"]+|[},]+&'
               )
                   AS rec
          FROM DUAL
        CONNECT BY LEVEL <= 11)
 WHERE rec IS NOT NULL;

Open in new window

jsondata is given here :

 {"1":{"YBG":1,"BNT":"vc{//}}vm"},"2":{"YBG":1,"BNT":"vc}{}}vm"},"3":{"YBG":1,"BNT":"vc{}}vm"}}

Open in new window

Note:Note:inside 'vcvm' IF i give any special character . it will take a whole string it should not trim any characters
result:
1":{"YBG":1,"BNT":"vc{//}vm"},"2":{"YBG":1,"BNT":"vc}{
3":{"YBG":1,"BNT":"vc{

expected result:
1":{"YBG":1,"BNT":"vc}{//}vm
2":{"YBG":1,"BNT":"vc}{}}vm
3":{"YBG":1,"BNT":"vc}{}}vm

Regards
Thomos

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deve_thomos

ASKER

hello slight wv

see my expeceted output:
1":{"YBG":1,"BNT":"vc}{//}vm
2":{"YBG":1,"BNT":"vc}{}}vm
3":{"YBG":1,"BNT":"vc}{}}vm

your output:
1":{"YBG":1,"BNT":"vc{//}}
2":{"YBG":1,"BNT":"vc}{}}
3":{"YBG":1,"BNT":"vc{}}

After that all charecters 'vm' characters are missing.

slightwv (䄆 Netminder)

Missed that.

Let me work on it.

slightwv (䄆 Netminder)

You had a typo from the solution in your previous question.

Based on the new requirement try this:

var JSONdata varchar2(100)

exec :JSONdata := '{"1":{"YBG":1,"BNT":"vc{//}}vm"},"2":{"YBG":1,"BNT":"vc}{}}vm"},"3":{"YBG":1,"BNT":"vc{}}vm"}}'


SELECT *
  FROM (SELECT
  	REGEXP_REPLACE(
                   REGEXP_SUBSTR(
                       :JSONdata,
                       '"[0-9]+".+?"}',
                       1,
                       LEVEL
                   )
                   ,
                   '^[{"]+|["}]+$'
               )
                   AS rec
          FROM DUAL
        CONNECT BY LEVEL <= 11)
 WHERE rec IS NOT NULL;

Open in new window

deve_thomos

ASKER

Thanks a lot...