Extract Middle numbers from a String

Transact-SQL
1

Hi experts,

Declare @File varchar(250)
Set @File = 'AAAA_106478963103_BL_862.tif'

I want to extract the numbers after and before the _
i.e. 106478963103

I have tried this

SELECT SUBSTRING(@File,CHARINDEX('underScore here',@File)+1,(((LEN(@File))-CHARINDEX('underScore Here', REVERSE(@File)))-CHARINDEX('_',@File)))

It returns 106478963103_BL_862
So I need to omit the _BL_862. This is where I get lost.

Thanks for any tips.

2 

;WITH test_data AS (
    SELECT 'AAAA_106478963103_BL_862.tif' AS [file]
)
SELECT d.[file], SUBSTRING(d.[file], NULLIF(ca1.start_of_value, 0), ca2.end_of_value - start_of_value + 1) AS value
FROM test_data d
CROSS APPLY (
    SELECT PATINDEX('%[_][0-9][0-9][0-9][0-9]%[_]%', d.[file]) + 1 AS start_of_value
) AS ca1
CROSS APPLY (
    SELECT CHARINDEX('_', d.[file], ca1.start_of_value) - 1 AS end_of_value
) AS ca2
1 Like
3

hi

another way to do this

select 
     value 
from 
   string_split(@File,'_') 
where 
    value NOT LIKE '%[a-zA-Z]%'

image

1 Like
4

Thanks @ScottPletcher and @harishgg1 . I used your solution @harishgg1 and that got me further down the road. It returns correct values for 96 percent of the rows. Thank you. I may post back if I can't get the other rows(the 4 percent) corrected.

5

hi

another way to this using translate

Declare @File varchar(250)
Set @File = 'AAAA_106478963103_BL_862.tif'

select @File 

select REPLACE(TRANSLATE(@File, 'abcdefghijklmnopqrstuvwxyz_.', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')

image