This discussion is archived
3 Replies Latest reply: Jan 18, 2013 8:47 AM by Frank Kulash RSS

Regular Expressions

Vortex13 Explorer
Currently Being Moderated
DB Version 11.1.0.7.0

I need to extract the first segment from a "-" delimited string when the org is 1 or 2, and the first two segments when the org is 3. I am pretty close, however the string can also be 1 segment without any "-", and in this case for org 3 I get an extra "-" at the end because of the concatenation. I know I could add to the decode to first check to see if the "-" is there (maybe change to a case statement), but I know there must be a more elegant way to do this by modifying my regular expressions.
WITH mydata
AS
(
SELECT 1 ORG, '1SEG1-SEG2-SEG3' RES FROM dual UNION
 SELECT 2, '2SEG1-SEG2-SEG3' FROM dual UNION
 SELECT 3, '3SEG1-SEG2-SEG3' FROM dual UNION
 SELECT 1, '1SEG1ONLY' FROM dual UNION
 SELECT 2, '2SEG1ONLY' FROM dual UNION
 SELECT 3, '3SEG1ONLY' FROM dual
 )
SELECT org, DECODE(org, 3, REGEXP_SUBSTR( res, '[^-]+', 1,1) || '-' || REGEXP_SUBSTR( res, '[^-]+', 1,2), REGEXP_SUBSTR( res, '[^-]+', 1,1))
  FROM mydata
Thanks in advance for your help.
--Johnnie                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 1. Re: Regular Expressions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    It's more efficient not to use regulat expressions for that.
    SELECT    org
    ,       res
    ,       SUBSTR ( res
                 , 1
               , NVL ( INSTR ( res
                                 , '-'
                          , 1
                          , CASE
                                    WHEN  org = 3
                             THEN  2
                             ELSE  1
                                END
                          ) - 1
                     , LENGTH (res)
                     )
               )          AS res_start
    FROM       mydata
    ORDER BY  org
    ,            res
    ;
  • 2. Re: Regular Expressions
    Vortex13 Explorer
    Currently Being Moderated
    OK, thanks Frank. As an educational exercise, how would you do it with regular expressions?
  • 3. Re: Regular Expressions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Vortex13 wrote:
    OK, thanks Frank. As an educational exercise, how would you do it with regular expressions?
    Here's one way:
    SELECT    org
    ,       res
    ,       REGEXP_SUBSTR ( res
                     , '^(-?[^-]*){1,' || CASE
                                        WHEN  org = 3
                                   THEN  '2}'
                                   ELSE  '1}'
                                    END
                   )     AS res_start
    FROM       mydata
    ORDER BY  org
    ,            res
    ;
    Here's how I see the problem.
    You have 2 different situations: org=3 and all others. What do these 2 siutations have in common, and what's different about them?
    What they have in common is: in both situations, you want to retrieve the text from the beignning of res up to, but not including, the n-th '-'. (There may be fewer than n hyphens, in which case you want the whole string.)
    What's different is the value of n. When org=3, then n=2: otherwise, n=1.
    Whether I use regular expressions or not, I want to find some way to leverage the similarities between the two situations. That is, I want to do something that is basically the same, but uses some conditional logic (such as a CASE expression) when the situation calls for it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points