createfunctionf_split(@cvarchar(2000),@splitvarchar(2))

returns@ttable(colvarchar(20))

as

begin

while(charindex(@split,@c)0)

begin

insert@t(col)values(substring(@c,1,charindex(@split,@c)-1))

set@c=stuff(@c,1,charindex(@split,@c),"")

end

insert@t(col)values(@c)

return

end

go

select*fromdbo.f_split("dfkd,dfdkdf,dfdkf,dffjk",",")

dropfunctionf_split

col

--------------------

dfkd

dfdkdf

dfdkf

dffjk

擴充套件資料

巧用SQL內建函式分割字串

createfunction[dbo].[fn_char_splitbystr](

@Stringnvarchar(4000),function

@Delimiternvarchar(100)

)

returns@ValueTabletable([FValue]nvarchar(4000),[FSerial]int)

as

begin

declare@valuenvarchar(4000),@valnvarchar(4000)

declare@NextStringnvarchar(4000),@Posint,@NextPosint,@CommaChecknvarchar(1),@idint

set@value=@String

set@id=1

set@NextString=""

set@CommaCheck=right(@value,1)

set@value=@value+@Delimiter+space(len(@Delimiter)-1)

set@Pos=charindex(@Delimiter,@value)

set@NextPos=1

while(@pos0)begin

set@NextString=substring(@value,1,@Pos-1)

set@val=@NextString

if@id>1set@val=substring(@val,len(@Delimiter),len(@val))

insertinto@ValueTable([FValue],[FSerial])VALUES(@val,@id)

set@value=substring(@value,@pos+1,LEN(@value))

set@NextPos=@Pos

set@pos=charindex(@Delimiter,@value)

set@id=@id+1

end

return

end