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 !