A question about string splitting. anxious

duckylam

declare str varchar (100)
set str = 'aa, b, cd, de, ed'
I want to split str into commas and recycle all the values ​​and add them. What should I do?
Thank you. Don't tell me to just remove the comma, I mainly loop after opening.

lj0917

create function f_split (@SourceSql varchar (8000), @StrSeprate varchar (10))
returns @temp table (a varchar (100))
/ *-A function that implements the split function
--date: 2005-4-20
--Author: Domino
* /
as
begin
    declare @i int
    set @ SourceSql = rtrim (ltrim (@SourceSql))
    set @ i = charindex (@ StrSeprate, @ SourceSql)
    while @i> = 1
    begin
        insert @temp values ​​(left (@ SourceSql, @ i-1))
        set @ SourceSql = substring (@ SourceSql, @ i + 1, len (@SourceSql)-@ i)
        set @ i = charindex (@ StrSeprate, @ SourceSql)
    end
    if @SourceSql <> '\'
       insert @temp values ​​(@SourceSql)
    return
end


select * from dbo.f_split ('Shandong: Jinan: Shandong: Jinan', ':')

select top 1 * from f_split ('Shandong: Jinan', ':')

fanfan1980

declare @str varchar (100)
set @ str = 'aa, b, cd, de, ed'
declare @sql varchar (8000)
select @ sql = 'select' ''
select @ sql = @ sql + replace (@str, ',', '' 'union all select' '') + '' ''
exec (@sql)

--result
----
aa
b
cd
de
ed