SQL Server - find nth occurrence in a string

Question

I have a table column that contains values such as abc_1_2_3_4.gif or zzz_12_3_3_45.gif etc.

I want to find the index of each underscore _ in the above values. There will only ever be four underscores but given that they can be in any position in the string, how can I achieve this?

I've tried the substring and charindex function, but I can only reliably get hold of the first one. Any ideas?

Answer 1

One way (2k8);

select 'abc_1_2_3_4.gif  ' as img into #T insert #T values ('zzz_12_3_3_45.gif')  ;with T as (     select 0 as row, charindex('_', img) pos, img from #T     union all     select pos + 1, charindex('_', img, pos + 1), img     from T     where pos > 0 ) select      img, pos  from T  where pos > 0    order by img, pos  >>>>  img                 pos abc_1_2_3_4.gif     4 abc_1_2_3_4.gif     6 abc_1_2_3_4.gif     8 abc_1_2_3_4.gif     10 zzz_12_3_3_45.gif   4 zzz_12_3_3_45.gif   7 zzz_12_3_3_45.gif   9 zzz_12_3_3_45.gif   11 

Update

;with T(img, starts, pos) as (     select img, 1, charindex('_', img) from #t     union all     select img, pos + 1, charindex('_', img, pos + 1)     from t     where pos > 0 ) select      *, substring(img, starts, case when pos > 0 then pos - starts else len(img) end) token from T order by img, starts  >>>  img                 starts  pos     token abc_1_2_3_4.gif     1       4       abc abc_1_2_3_4.gif     5       6       1 abc_1_2_3_4.gif     7       8       2 abc_1_2_3_4.gif     9       10      3 abc_1_2_3_4.gif     11      0       4.gif   zzz_12_3_3_45.gif   1       4       zzz zzz_12_3_3_45.gif   5       7       12 zzz_12_3_3_45.gif   8       9       3 zzz_12_3_3_45.gif   10      11      3 zzz_12_3_3_45.gif   12      0       45.gif