decomposition of string

Posted on 2014-08-13
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.



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

select regexp_count('/abc/123PQR7890', '/'), regexp_count('/qwerty/1LMN234567890/xyz', '/') from dual;
Question by:loginboy
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.
    LVL 76

    Accepted Solution

    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

    LVL 73

    Expert Comment

    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 Comment

    @ slightwv

    You solution works for me.

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

    Author Comment

    @ 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?
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This video shows how to recover a database from a user managed backup

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now