Get data between nth occurrence of two characters

sql serversql-server-2012

I'm trying to extract a certain data from below two examples and can't seems to get it.

From first string I need this value 99068001 and from second string I need this value
42135001

I think it would work if the second segment (data between two '/') from the end is numeric then get 3rd segment from the end else 4th segment from the end.

Thank you !

./Comp/10-00001/130---30-00003/40-00620/50-01316/60-23615/99068001/0099068001/Patient Worker
./Comp/10-00001/130---30-00003/40-00630/50-00407/60-31146/42135001/0042135001/Back of House - Culinary/Utility Worker

Here is the SQL I inherited from this project.

    WITH SampleData (TESTDATA) AS
(
    SELECT './Comp/10-00001/130---30-00003/40-00620/50-01316/60-23615/99068001/0099068001/Patient Worker' UNION ALL 
    SELECT './Comp/10-00001/130---30-00003/40-00630/50-00407/60-31146/42135001/0042135001/Back of House - Culinary/Utility Worker'   
    )

SELECT RIGHT(SUBSTRING((TESTDATA),CHARINDEX('/',TESTDATA) - 8, 
 LEN(TESTDATA) - CHARINDEX('/',TESTDATA )-  CHARINDEX('/',TESTDATA )- CHARINDEX('/',REVERSE(TESTDATA))),8) as Data
FROM SampleData

Here is the current Result

Data
99068001
of House

Here is the desired Result

Data
99068001
42135001

Thank you and god bless !

TB

Best Answer

Considering you need to find the first occurence of 8 digits between 2 '/', you could use PATINDEX:

WITH SampleData (TESTDATA) AS
(
    SELECT './Comp/10-00001/130---30-00003/40-00620/50-01316/60-23615/99068001/0099068001/Patient Worker' UNION ALL 
    SELECT './Comp/10-00001/130---30-00003/40-00630/50-00407/60-31146/42135001/0042135001/Back of House - Culinary/Utility Worker'   
    )
SELECT SUBSTRING(TESTDATA,PATINDEX('%/[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]/%',TESTDATA)+1,8)
FROM SampleData
Related Question