Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

How to get this query ?

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
deve_thomos
Asked:
deve_thomos
  • 7
  • 6
  • 3
1 Solution
 
sdstuberCommented:
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
 
deve_thomosAuthor Commented:
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
 
sdstuberCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
SurranoCommented:
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
 
deve_thomosAuthor Commented:
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
 
sdstuberCommented:
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
 
SurranoCommented:
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
 
deve_thomosAuthor Commented:
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
 
sdstuberCommented:
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
 
SurranoCommented:
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
 
sdstuberCommented:
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
 
deve_thomosAuthor Commented:
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
 
sdstuberCommented:
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
 
deve_thomosAuthor Commented:
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
 
sdstuberCommented:
>> 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
 
deve_thomosAuthor Commented:
Thanks a lot
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now