sql - 使用sql在文件中搜索字符串

Question

我有类似的东西

set @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'
SELECT SUBSTRING(@file,CHARINDEX(',',@file)+1, CHARINDEX(',',@file,CHARINDEX(',',@file)+1) -CHARINDEX(',',@file)-1) as My_string 

这将打印出来

     My_string
    ----------------
   1|     bbbb

我怎样才能让它打印出来？

   My_string
 ------------
1|   bbbb
2|   cccc
3|   dddd
4|   eeee

Answer 1

试试这个代码。，(引用自 Turning a Comma Separated string into individual rows )

declare @file varchar(max)
set @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'

;with tmp(DataItem, Data) 
as (
select LEFT(@file, CHARINDEX(',',@file+',')-1),
    STUFF(@file, 1, CHARINDEX(',',@file+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > '')
select DataItem from tmp