declare @str varchar(100) declare @t table(name varchar(10)) set @str = 'aa;bbb;c;dddd'while charindex(';',@str + ';')> 0 begin insert @t select substring(@str,1,charindex(';',@str + ';')-1) set @str = stuff(@str,1,charindex(';',@str + ';'),'') end select * from @t/* aa bbb c dddd */
Create Table A (IText Varchar(100)) Insert A Select 'aa;bbb;c;dddd' GO Select Top 1000 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns BSelect IText = Substring(A.IText, B.ID, CharIndex(';', A.IText + ';', B.ID) - B.ID) From A, #T B Where Substring(';' + a.IText, B.id, 1) = ';' Order By ITextDrop Table #T GO Drop Table A --Result /* IText aa bbb c dddd */
如果數據量不大,可以用While做循環的方式,但是如果數據量比較大,可以使用借用臨時表的方式。
create function f_splitStr(@s varchar(8000),@split varchar(10)) returns @tb table(col varchar(100)) as begin declare @t table(id int identity,b bit) insert @t select top 500 0 from syscolumns a,syscolumns b insert @tb select substring(@s,id,charindex(@split,@s+@split,id)-id) from @t where id<=len(@s+'!') and charindex(@split,@split+@s,id)=id return end goselect * from dbo.f_splitStr('aa;bbb;c;dddd',';') col -------- aa bbb c dddd
declare @t table(name varchar(10))
set @str = 'aa;bbb;c;dddd'while charindex(';',@str + ';')> 0
begin
insert @t select substring(@str,1,charindex(';',@str + ';')-1)
set @str = stuff(@str,1,charindex(';',@str + ';'),'')
end
select * from @t/*
aa
bbb
c
dddd
*/
(IText Varchar(100))
Insert A Select 'aa;bbb;c;dddd'
GO
Select Top 1000 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns BSelect
IText = Substring(A.IText, B.ID, CharIndex(';', A.IText + ';', B.ID) - B.ID)
From A, #T B
Where Substring(';' + a.IText, B.id, 1) = ';'
Order By ITextDrop Table #T
GO
Drop Table A
--Result
/*
IText
aa
bbb
c
dddd
*/
returns @tb table(col varchar(100))
as
begin
declare @t table(id int identity,b bit)
insert @t select top 500 0 from syscolumns a,syscolumns b insert @tb select substring(@s,id,charindex(@split,@s+@split,id)-id)
from @t
where id<=len(@s+'!') and charindex(@split,@split+@s,id)=id return
end
goselect * from dbo.f_splitStr('aa;bbb;c;dddd',';') col
--------
aa
bbb
c
dddd