Regex to turn /01/02/007/... into 01/02 and /01 into 01/NA - is it possible?

Question

I have filesystem-like (but numerical) path of arbitrary length which I need to canonicalize to a fixed depth (2 or 3). Basically, I want 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 do with using Oracle's regexp_replace() function, which apparently impelements the extended POSIX regexp standard.

What I have so far is this: regexp_replace(path,'/([^/]+(/[^/]+)?).*','\1'); But I'm having trouble with the NA part.

Answer 1

You don't actually need a regex here, just use substr with instr, eg.

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

You will need to catch the case where there isn't a fourth forward slash in the string.

Answer 2

This seems to work:

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 simply an exercise in regular expressions, then it doesn't meet your requirements.