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.
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.
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:
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