Regex to turn / 01/02/007 / ... on 01/02 and / 01 on 01 / NA - is this possible?

Question

I have a file-like (but numeric) path of arbitrary length, which I need to canonize to a fixed depth (2 or 3). Basically, I need this result:

/01/02/007/008  ->  01/02/007
/01/02/007      ->  01/02/007
/01/02          ->  01/02/NA
/01             ->  01/NA/NA

I want to use an Oracle function regexp_replace()that apparently impelements extends the PREIX regexp standard.

What I still have: regexp_replace(path,'/([^/]+(/[^/]+)?).*','\1'); But I have problems with the part NA.

Answer 1

It works:

SQL> WITH q AS (
SELECT '/01/02/007/008/009' a FROM dual
UNION
SELECT '/01/02/007/008' FROM dual
UNION
SELECT '/01/02/007' FROM dual
UNION
SELECT '/01/02' FROM dual
UNION 
SELECT '/01' FROM dual)
SELECT a, CASE WHEN INSTR(a,'/',1,4) > 0 THEN SUBSTR(a,2,INSTR(a,'/',1,4)-2)
               WHEN INSTR(a,'/',1,3) > 0 THEN SUBSTR(a,2)
               WHEN INSTR(a,'/',1,2) > 0 THEN SUBSTR(a,2)||'/NA'
               ELSE SUBSTR(a,2)||'/NA/NA'
          END RESULT
  FROM q;          

A                  RESULT
--------------     -------------------
/01                01/NA/NA
/01/02             01/02/NA
/01/02/007         01/02/007
/01/02/007/008     01/02/007
/01/02/007/008/009 01/02/007

SQL> 

Of course, if this is just an exercise in regular expressions, then it does not meet your requirements.

Answer 2

, substr instr, .

SUBSTR(path, 0, instr(path, '/', 1, 4)-1)

, .