1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR driving me nuts

Discussion in 'SQL PL/SQL' started by Booleys, Sep 10, 2013.

  1. Booleys

    Booleys Guest

    Dear members of club-oracle,

    I've got a database-table with a field that contains the following data in it:

    {0:123456789}
    {0:5432}
    <space> {0:220} <space>
    etc...


    I only want: 123456789, 5432 and 220 as a result to match this ID with another field in a WHERE clause. There are no fixed positions in the field, so spaces before the {0: and after the } are possible.

    Can somebody help me to get the right REGEXP_LIKE or other REGEXP function?

    Thanks in advance!
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi,

    If the searched values are to be extracted as distinct tokens, then apart from the regular expression pattern you will also need a row generator in order to split tokens whithin each field. I tried the following:

    Code (SQL):

    SET PAGESIZE 1000;
    SET LINESIZE 200;


    COLUMN row_id FORMAT a30;
    COLUMN token FORMAT 99999999999;

    CREATE TYPE intTab_ty IS TABLE OF NUMBER
    /
    SHOW ERRORS;


    CREATE TABLE tmptab AS
        WITH subqFact AS
        (
            SELECT '{0:123456789}   {0:5432}   {0:220}' AS colval
            FROM "PUBLIC".dual UNION ALL
            SELECT '{0:12345}   {0:11}   {0220}'        AS colval
            FROM "PUBLIC".dual UNION ALL
            SELECT '{0}   {0:11}   {0220}'              AS colval  
            FROM "PUBLIC".dual UNION ALL
            SELECT '    {0:999}   {0:555}   {0:666}'    AS colval
            FROM "PUBLIC".dual UNION ALL
            SELECT '{0:1111}  {0:22}{0:999}   '         AS colval
            FROM "PUBLIC".dual
        )
        SELECT t1.colval
        FROM   subqFact t1;
       
       
    SELECT t4.row_id, t4.token
    FROM
    (
    SELECT  rowid row_id,
            regexp_replace
            (
                regexp_substr
                (
                    t2.colval,
                    '[ ]*\{([[:digit:]])+\:([[:digit:]])+\}[ ]*',
                    1,
                    t3.column_value
                ),
                '(0:)|[ {}]'
            ) token
    FROM
    (
        SELECT  t1.colval
        FROM    tmptab t1
        WHERE   regexp_like(t1.colval,
                    '^([ ]*(\{([[:digit:]])+\:([[:digit:]])+\})+[ ]*)+$')
    ) t2
    CROSS JOIN
        TABLE
        (
            CAST
            (
                MULTISET
                (
                    SELECT  level lvl
                    FROM    "PUBLIC".dual
                    CONNECT BY level <=
                        regexp_count
                        (
                            t2.colval,
                            '[ ]*\{([[:digit:]])+\:([[:digit:]])+\}[ ]*'
                        )
                )
                AS intTab_ty  
            )
        ) t3
    ) t4
    GROUP BY t4.row_id, t4.token
    ORDER BY t4.row_id, t4.token;
     
    Which gives the following output

    Code (Text):

    ROW_ID                 TOKEN
    ------------------------------ ------------------------------------
    AAAWyQAAGAAAAIDAAA         123456789
    AAAWyQAAGAAAAIDAAA         220
    AAAWyQAAGAAAAIDAAA         5432
    AAAWyQAAGAAAAIDAAD         555
    AAAWyQAAGAAAAIDAAD         666
    AAAWyQAAGAAAAIDAAD         999
    AAAWyQAAGAAAAIDAAE         1111
    AAAWyQAAGAAAAIDAAE         22
    AAAWyQAAGAAAAIDAAE         999

    9 rows selected.