SQL Server query to substr between 2 characters from end of the string -

i new sql. please help.

i have table column values below

xyz_3244h_v65_c005_z1234567455_p_f_20160127_0137.txt ertl_3244h_v65_c005_yiiuuuuuuab_1p_w_20160316_1227.txt wtern_3244h_vtwt05_twjeonsosjs_qwt_n_20160316_0937.txt 

i want query outputs

z1234567455 yiiuuuuuuab twjeonsosjs 

i need extract value between 4th , 5th underscores counted reverse of string. please help. tia.

using numbers table can accomplish this,all have create numbers table..

declare @string varchar(max)  set @string='_'+'xyz_3244h_v65_c005_z1234567455_p_f_20160127_0137.txt'+'_'   select  substring (@string,n+1,charindex('_',@string,n+1)-n-1) numbers   substring(@string,n,1)='_' ,  n<len(@string) 

you can use rownumber select values in place below..

select  substring (@string,n+1,charindex('_',@string,n+1)-n-1),row_number() on (order charindex('_',@string,n+1)) rn numbers   substring(@string,n,1)='_' ,  n<len(@string) 

then use cte number @ place like

;with cte ( select  substring (@string,n+1,charindex('_',@string,n+1)-n-1) splitstrig,row_number() on (order charindex('_',@string,n+1)) rn numbers   substring(@string,n,1)='_' ,  n<len(@string) ) select * cte rn=5