0

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

1
  • I just checked with the client and the easiest way to grab what I'm looking for is to get the segment between the '/' that is 8 digit long (always) and numeric. I guess I can start to eliminate each segment until I get to the one with 8 numeric digits. Sep 10, 2021 at 6:42

1 Answer 1

1

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
1
  • Man this is genius, exactly what I was looking for. Thank you so much Eric Prevost ! Sep 11, 2021 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.