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