[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

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;
  • 3
  • 2
1 Solution
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

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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