SQL Server - 在字符串中查找第 n 个出现

2022-01-31 00:00:00 sql-server

我有一个表列，其中包含 abc_1_2_3_4.gif 或 zzz_12_3_3_45.gif 等值.

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?

我已经尝试过 substring 和 charindex 函数，但我只能可靠地掌握第一个.有什么想法吗?

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

推荐答案

单向(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

更新

;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

相关文章