Use SQL to extract characters with multiple back slashes

Question

I have a string:

 \_AAA\DDDD\09090\longtest

How can I extract just the string 09090?
There will be many strings like these and I would like to extract the string between the 3rd and fourth back slash.

Answer 1

I'll bite. Here's an Oracle solution that grabs what's in the 3rd field:

select regexp_substr('\_AAA\DDDD\09090\longtest', '[^\]+', 1, 3) from dual;

Technically it grabs the 3rd match of a set of characters that do not match a backslash.

I just realized it does not allow for a null field though.

This works:

select nvl(regexp_substr( str, '([^\\]*)\\{0,1}', 1, level, 'i', 1 ), 'null') part
from ( select '\_AAA\DDDD\09090\longtest' str from dual )
where level = 4
connect by level <= regexp_count( str, '\' ) + 1;

From here:

Regex - Don't match nulls after words

Answer 2

A simple (though not necessarily efficient) solution for SQL Server is:

SELECT Substring(text, first, second - first) 
FROM   (SELECT text, 
               Charindex('\', text, Charindex('\', text, Charindex('\', text)+1) 
               +1) 
               + 1                       first, 
               Charindex('\', text, Charindex('\', text, Charindex('\', text, 
                                    Charindex 
                                    ('\', 
                                    text)+1)+1) 
                                    + 1) second 
        FROM   table1)T 

See a working example on SQL Fiddle

---

EDIT

A better answer uses a recursive query.

Try something like this:

WITH A 
     AS (SELECT CAST(0 AS BIGINT)     AS idx1, 
                CHARINDEX('\', TEXT) idx2, 
                TEXT, 
                0                     AS rn 
         FROM   TABLE1 
         UNION ALL 
         SELECT CAST(IDX2 + 1 AS BIGINT), 
                CHARINDEX('\', TEXT, IDX2 + 1), 
                TEXT, 
                RN + 1 
         FROM   A 
         WHERE  IDX2 > 0) 
SELECT SUBSTRING(TEXT, IDX1, IDX2 - IDX1) 
FROM   A 
WHERE  RN = 3 

It is much better since you can easily adapt it to extract any part of the string, not just the third.

See a working example of this on SQL Fiddle