Solved

How to get this query ?

Posted on 2014-07-22
16
265 Views
Last Modified: 2014-07-23
Hi Expert,
i have below query...

SELECT rtrim(ltrim(regexp_substr(rtrim(ltrim('or{ac}le}' ,'{') ,'}') ,'[^}]+' ,1 ,LEVEL) ,',"') ,'"') AS rec
FROM dual
  CONNECT BY LEVEL <= LENGTH('or{ac}le}') - LENGTH(REPLACE('or{ac}le}' ,'},"'));

Open in new window


Output:or{ac
 
Expected output:   or{ac}le

Regards
Thomos
0
Comment
Question by:deve_thomos
  • 7
  • 6
  • 3
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40212849
I don't understand the question.  You have the expected output as a literal string within the original query.

If that's an input, simply leave it alone.

select 'or{ac}le'  from dual

Open in new window


If something else is supposed to be the input that produces that output,  then what is that input?
0
 

Author Comment

by:deve_thomos
ID: 40212896
Helloo Sdstubber,

I am giving as input here  like   or{ac}le}  
but here expected should be first and last cully braces should removed.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40212901
sorry I didn't see the trailing "}" in the original amidst all the other syntax, but removing the trailing is easy, just a single rtrim

select rtrim('or{ac}le}','}') from dual


Since you already had trim functions in your original query I'm not sure what you were trying to do with the connect by.

Are you oversimplifying your example and thus masking some of the needed functionality?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 40213692
Why do you need the regexp_substr call? Can you provide an example where it makes sense, i.e. rtrim(ltrim(...)...) does not provide the result as you desire?
0
 

Author Comment

by:deve_thomos
ID: 40214359
Can you plaese tell  me how i will get this query  ?
This way how can i achieve. existing is there i dont wat to again change the there is a chances of brekage of json so i am not awre about . so can plaese help in this way how can i achieve?
(SELECT rtrim(ltrim(regexp_substr(rtrim(ltrim( '{"1":{"BCC":1,"BNT":"fffffrh}fhhrfrhfhrf"}}' ,'{') ,'}') ,'[^}]+' ,1 ,LEVEL) ,',"') ,'"') AS rec
                                                                      FROM dual
                                                         CONNECT BY LEVEL <= LENGTH( '{"1":{"BCC":1,"BNT":"sdvsdvsdvdsvsdvsdvsdvdsvsdvdsvdsvdsv"}}') - LENGTH(REPLACE( '{"1":{"BCC":1,"BNT":"sdvsdvsdvdsvsdvsdvsdvdsvsdvdsvdsvdsv"}}' ,'},"'))
                                                                 );

Open in new window


output: 1":{"BCC":1,"BNT":"fffffrh
expected :    1":{"BCC":1,"BNT":"fffffrh}fhhrfrhfhrf
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40214387
what is the input in that example?

also, think about what you are asking,  you don't want to make a change, but you want help fixing the query?  

How can that happen? Anything I suggest that is a fix, means you'll have to change something in the query.

Don't try to preserve the current logic - for one, you already know it doesn't work.  Second, what you're trying to do is just a parsing task - nothing special about it.

Parsing is just inputs, rules and outputs.

If you can describe your rules I can code them.
But at bare minimum you have to provide input for expected output.
You don't need to post the incorrect output.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 40214618
For the new example you provided the situation is the same:
you use rtrim(ltrim(regexp_subs(rtrim(ltrim(...)...)...)...)...) which provides wrong output.
if you use only the internal rtrim(ltrim(...)...) expression like this:
SELECT rtrim(ltrim( '{"1":{"BCC":1,"BNT":"fffffrh}fhhrfrhfhrf"}}' ,'{') ,'}') AS rec
FROM dual
CONNECT BY LEVEL <= LENGTH( '{"1":{"BCC":1,"BNT":"sdvsdvsdvdsvsdvsdvsdvdsvsdvdsvdsvdsv"}}') - LENGTH(REPLACE( '{"1":{"BCC":1,"BNT":"sdvsdvsdvdsvsdvsdvsdvdsvsdvdsvdsvdsv"}}' ,'},"'));

Open in new window


then it's the expected output. Well assuming that you mistyped the expected output. You wrote:
 expected :    1":{"BCC":1,"BNT":"fffffrh}fhhrfrhfhrf
I think you meant: (note the closing quote)
 expected :    1":{"BCC":1,"BNT":"fffffrh}fhhrfrhfhrf"

Also, what's the correlation of the seemingly unrelated strings of the select clause (...ffff...) and the connect clause (...sdvsdv...)?
0
 

Author Comment

by:deve_thomos
ID: 40214645
Below is my query , sorry  i forgot give you  give proper data
(SELECT rtrim(ltrim(regexp_substr(rtrim(ltrim( '{"1":{"ACC":1,"YBT":"dambydamby"}}' ,'{') ,'}') ,'[^}]+' ,1 ,LEVEL) ,',"') ,'"') AS rec
                                                                      FROM dual
                                                         CONNECT BY LEVEL <= LENGTH( '{"1":{"ACC":1,"YBT":"damby}damby"}}') - LENGTH(REPLACE( '{"1":{"ACC":1,"YBT":"damby}damby"}}' ,'},"'))
                                                                 );

Open in new window


Result:   1":{"ACC":1,"YBT":"damby
expected result:  1":{"ACC":1,"YBT":"damby}damby
 
yes I want to remove curly brackets that occur at the very beginning of end of the string, but leave curly brackets in the middle on above  query and also removing double-quotes at the very beginning and end of the string
Not:   remove curly brackets Very beginning is handled on above query .
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40214665
yes I want to remove curly brackets that occur at the very beginning of end of the string, but leave curly brackets in the middle on above  query and also removing double-quotes at the very beginning and end of the string

thank you!!! that's a a very simple rule.

again -posting the query without specifying the input doesn't really help.  

given input :  '{"1":{"ACC":1,"YBT":"damby}damby"}}'
and output:  1":{"ACC":1,"YBT":"damby}damby

with that rule is very easy


select regexp_replace('{"1":{"ACC":1,"YBT":"damby}damby"}}','^[{"]+|[}"]+$') from dual

Open in new window


^[{"]+ -- this means { or " at the beginning of string
[}"]+$  -- this means } or " at the end of string

so, simply remove all instances of them by replacing them with NULL (the null is implicit by not specifying the 3rd parameter)

Since each of the regexps include a begin/end qualifier, all curlies and quotes in the middle will be ignored
0
 
LVL 8

Expert Comment

by:Surrano
ID: 40214736
Well then it's just the regexp_substr which is unnecessary. I don't know if it's any better than a single regexp_replace, maybe a bit faster if it matters:

SELECT rtrim(ltrim(rtrim(ltrim( '{"1":{"BCC":1,"BNT":"fffffrh}fhhrfrhfhrf"}}' ,'{') ,'}') ,',"') ,'"') AS rec
FROM dual;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40214902
using rtim and ltrim doesn't work reliably unless you know for sure how many levels you'll have and in what order of nesting

the regexp's I posted above will always work regardless of how many levels of nesting quotes and braces or in what order.

for example  {"{"{"{{{{{{{}}}}}}}"}"}"}

trim methods wouldn't work unless you used 26 calls - yes, that's an extreme example simply to illustrate the problem

The regexp works as is with no changes even on something extreme like this


also, even if you do go with the trim version it's not necessary to use ltrim and rtrim for quotes, just use TRIM(both ...
0
 

Author Comment

by:deve_thomos
ID: 40214999
Hello expert ,
I think i was not able to  ask proper question, i mean not able to make you understand. see this should come in three node.

(SELECT regexp_replace('{"1":{"ACC":1,"YBT":"damby}damby"}},"2":{"ACC":1,"YBT":"damby}damby"}},"3":{"ACC":1,"YBT":""}}','^[{"]+|[}"]+$') AS rec
FROM dual
);

Open in new window


Result:1":1":{"ACC":1,"YBT":"damby}damby"}},"2":{"ACC":1,"YBT":"damby}damby"}},"3":{"ACC":1,"YBT":
Expected Result:
    1":{"ACC":1,"YBT":"damby}damby
    2":{"ACC":1,"YBT":"damby}damby
    3":{"ACC":1,"YBT":

Regrads
Thomos
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40215024
I 'm not sure what your last post is trying say.

I think you're trying to change the question.  If so,  you should close this question, assuming this question has been answered and I think it has; and open a new one with the new requirements.

In your new question - please don't post a query that doesn't work.

Post input data and expected output data and rules of what should be parsed.
 Let the responders come up with the query.
0
 

Author Comment

by:deve_thomos
ID: 40215036
Hello Ststuber,
 please see my question you can under stand what exactly i want. still if you are not getting i will attach here  expected output. i am trying to change the question.

Regards
Thomos
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40215044
>> you can under stand what exactly i want.

no, I can't

>>>  i am trying to change the question.

Then you should open a new question.

Do you need further help on this question?
0
 

Author Closing Comment

by:deve_thomos
ID: 40215051
Thanks a lot
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now