decomposition of string

Hey Gurus,

Please advice the best way to parse and get 123PQR7890 and 1LMN234567890 ? length of this ID can vary. Thanks in advance.



Also, is regexp_count optimal to get the count or occurrence?

select regexp_count('/abc/123PQR7890', '/'), regexp_count('/qwerty/1LMN234567890/xyz', '/') from dual;
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What are the actual rules?

It looks like anything after the first '/' that isn't another '/'.

From the regexp_count it looks like you are counting the number of '/'
That would probably be faster with :

length(string) - length(replace(string,'/'))

The count versus the extract that you posted really don't match.   Please clarify the actual requirements.
slightwv (䄆 Netminder) Commented:
If you want the 'second' set of characters that isn't a '/' try this:
with mydata as (
select '/abc/123PQR7890' mycol from dual
union all
select '/abc/1LMN234567890/xyz' mycol from dual
select regexp_substr(mycol,'[^/]+',1,2) from mydata

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If you have to parse a lot of data, regexp searches can be expensive, for something simple like 2nd substring extraction you can use instr and substr more effeciently

WITH yourdata
     AS (SELECT '/abc/123PQR7890' str FROM DUAL
         UNION ALL
         SELECT '/abc/1LMN234567890/xyz' FROM DUAL)
select case when s > 0 then substr(str,s+1,e-s) end from
(select instr(str,'/',1,2) s, nvl(nullif(instr(str,'/',1,3),0)-1,length(str)) e, str from yourdata)

Note: like slightwv's regexp - this assumes you want the second substring in a "/" delimited string

If you need something else, please elaborate on how to identify the target substring
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

loginboyAuthor Commented:
@ slightwv

You solution works for me.

select regexp_substr(mycol,'[^/]+',1,2) from mydata
loginboyAuthor Commented:
@ sdstuber

Actually this is not a table data. Procedure receives nested table with 1 field having this URI.
And length of this string is not large max 35 chars . will using reg_expr functions degrade the performance?
slightwv (䄆 Netminder) Commented:
regexp functions aren't the most efficient.  It is all about the number of times you call them and the resources available.

Regardless of how you use them, it is all about the numbers.

Say 10,000 times a day, probably doesn't matter.  100 million times a day, then you might need to rethink them.

If your system is already struggling with performance, you might be concerned.  If the system isn't working hard at all, why bother.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.