Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to get this query ?

Posted on 2014-07-22
16
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
16 Comments
 
LVL 74

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 74

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 74

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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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 74

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 74

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 74

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

670 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