Jump to content
Sam@11

Check Last Position at which value occurs through pl/sql

Recommended Posts

A+++500+read++

 

I want the occurence of last '+'  or in other words,total count of '+' before last value 'read' through PL/SQL at which value occurs.First value is 'A',Second value is '500' , third value is 'read'.Here last value is 'read' and position of '+' at which last value 'read' occurs is 4.I want this output 4 through query.Just want occurence of Highlighted '+' or in other words,total count of '+' before last value 'read'.Max position of '+' at which last value is there.Here output should be 4.

Second i want total number of values that occurs between '+'..Here three values namely A,500,read is there.I want count of values means how many values exist between '+'.Output should be 3.

Share this post


Link to post
Share on other sites

Hello Sam,

Adding to Mr. Burleson, You can do it with the help of REGEXP_SUBSTR and REGEXP_COUNT as shown in the below query,
 

with t1 as (select 'A+++500+read++' col1 from dual)
select regexp_count(regexp_substr(col1,'[^read]+'),'\+') from t1;

Thanks,

Boobal Ganesan

Share this post


Link to post
Share on other sites

Hi Sam,

6 hours ago, Sam@11 said:

Second i want total number of values that occurs between '+'..Here three values namely A,500,read is there.I want count of values means how many values exist between '+'.Output should be 3.

To your second question, we can quickly achieve by using REGEXP_COUNT as below,

with t1 as (select 'A+++500+read++' col1 from dual)
select regexp_count(col1,'[[:alnum:]]+') from t1;

Here we are just checking for alpha numeric words.

 

Thanks,

Boobal Ganesan

Share this post


Link to post
Share on other sites
15 minutes ago, boobalganesan said:

Hello Sam,

Adding to Mr. Burleson, You can do it with the help of REGEXP_SUBSTR and REGEXP_COUNT as shown in the below query,
 


with t1 as (select 'A+++500+read++' col1 from dual)
select regexp_count(regexp_substr(col1,'[^read]+'),'\+') from t1;

Thanks,

Boobal Ganesan

 

Share this post


Link to post
Share on other sites

@boobalganesan

Hello Boobal

 

Here 3rd value is 'read' bt its not like that we will always get 'read' value only..We can get different values.

 

Just like A++++++USA+

A+++++++London+++++Newyork

 

In every case,occurence of last '+' at which value occurs is needed.Here just for sample,i gave last value 'read' but last values can be different.

 

& in second case bettween two '+',even if .(dot) occurs just like A+++++.++,we have to consider .(dot) as value.Anythng apart from '+' will be considered as value..

 

      

Share this post


Link to post
Share on other sites

My bad!

First Answer,

with t1 as (select 'A+++500+read++' col1 from dual)
select regexp_count(substr(col1,1,regexp_instr(col1,'[^\+]+',1,regexp_count(col1,'[^\+]+'))-1),'\+') from t1;

Second Answer,

with t1 as (select 'A+++500+read++' col1 from dual)
select regexp_count(col1,'[^\+]+') from t1;

Let me know how this goes for you.

Thanks

Boobal Ganesan

 

Share this post


Link to post
Share on other sites

@boobalganesan

Hello Ganesan

 

Thanks for your solution.Both is working fine according to my need.

One more thing i wna ask,if for the same data

A+++500+read++

,i want to capture position for each value means at which '+' postion  value occurs or in other words count of '+' before each value,what should i do??

In this data,for 'A',position should be 0,for '500' position should be 3,for 'read,it should   be 4.Output should be 0,3,4.Or i  concatenate +||A+++500+read++ and then find count of '+' before each value and get that output as 1,4,5.What should be the query for this?

Share this post


Link to post
Share on other sites

@boobalganesan

One more thing i wna ask,if for the same data

A+++500+read++

,i want to capture position for each value means at which '+' postion  value occurs or in other words count of '+' before each value,what should i do??

In this data,for 'A',position should be 0,for '500' position should be 3,for 'read,it should   be 4.Output should be 0,3,4.Or i  concatenate +||A+++500+read++ and then find count of '+' before each value and get that output as 1,4,5.What should be the query for this?

Share this post


Link to post
Share on other sites

@Sam@11

Sorry for the delayed response :(

Please find the query below.
 

SELECT
    LISTAGG(
        regexp_instr(
            'A+++500+read++',
            regexp_substr(
                'A+++500+read++',
                '[^+]+',
                1,
                level
            )
        ),
        ','
    ) WITHIN GROUP(ORDER BY ROWNUM) col1
FROM
    dual
CONNECT BY
    level <= regexp_count(
        'A+++500+read++',
        '[^+]+'
    );

The result is, 

1,5,9

Oracle counts from 1, not 0.

 

Thank you,

Boobal Ganesan

Share this post


Link to post
Share on other sites

@boobalganesan

Thanks boobal for you are response.

Bt here 1,5,9 i m getting.I want the position of each value means at which position of '+',value occurs or in other words count of '+' before each value.Here

data is 'A+++500+read++'...now i add one more '+' before 'A'..now data looks like '+A+++500+read++'.Now i want count of '+' before each value.Here 3 values namely 'A','500',read'

is there.Now i want output as 1,3,5.

A+++500+read++

Share this post


Link to post
Share on other sites

Hi Sam,

If input is "+A+++500+read++", the output will be 1 for "A", 3 for "500" and 1 for "read" isnt it? So the result will be 1,3,1. 

How are you expecting a result of 1, 3, 5 ?

 

Thank you,

Boobal Ganesan

Share this post


Link to post
Share on other sites

@boobalganesan

Hello boobal

Here we have to take count of '+' before each value...Before 'A',1 '+' is there,so it will give 1,before '500',total 4 '+' is there from sarting means including '+' before 'A' also,then total count of '+' before '500' will be 4 and total count of '+' before 'read' will be 5 which will include '+' before '500' also & '+' before 'A' also...means count of '+' before each value starting from the start.

 

Then output will be 1,3,5

Share this post


Link to post
Share on other sites
11 minutes ago, Sam@11 said:

@boobalganesan

Hello boobal

Here we have to take count of '+' before each value...Before 'A',1 '+' is there,so it will give 1,before '500',total 4 '+' is there from sarting means including '+' before 'A' also,then total count of '+' before '500' will be 4 and total count of '+' before 'read' will be 5 which will include '+' before '500' also & '+' before 'A' also...means count of '+' before each value starting from the start.

 

Then output will be 1,3,5

I've highlighted the counts in your text. It says 1,4,5 and not 1,3,5.

 

Thanks,

Boobal Ganesan

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×