3 Replies Latest reply: Jan 18, 2013 10:47 AM by Frank Kulash RSS

    Regular Expressions

    Vortex13
      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
          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
            OK, thanks Frank. As an educational exercise, how would you do it with regular expressions?
            • 3. Re: Regular Expressions
              Frank Kulash
              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.