decomposition of string

loginboy
loginboy used Ask the Experts™
on
Hey Gurus,

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

/abc/123PQR7890

/abc/1LMN234567890/xyz

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

select regexp_count('/abc/123PQR7890', '/'), regexp_count('/qwerty/1LMN234567890/xyz', '/') from dual;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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.
Most Valuable Expert 2012
Distinguished Expert 2018
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

Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
@ slightwv

You solution works for me.

select regexp_substr(mycol,'[^/]+',1,2) from mydata
/

Author

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?
Most Valuable Expert 2012
Distinguished Expert 2018

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial