We help IT Professionals succeed at work.

decomposition of string

loginboy
loginboy asked
on
190 Views
Last Modified: 2014-08-20
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
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

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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.