SQL Split string (1/2)

This article about the SQL split string oh, how to do the SQL string splitting operation, in the Mmsql server to be split will be used to the stored procedures Oh, in fact, the general SQL can not achieve ha.

Use a temporary table as an array

Create function F_split (@c varchar), @split varchar (2))
Returns @t table (col varchar (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 * from Dbo.f_split (' dfkd,dfdkdf,dfdkf,dffjk ', ', ')

Drop function F_split
Col
--------------------
Dfkd
Dfdkdf
Dfdkf
Dffjk

(The number of rows affected is 4 rows)

Second, according to the specified symbol segmentation string, return the number of elements after the split, the method is very simple, is to see how many delimiters in the string, and then add one, is the result of the request.

Create function Get_strarraylength
(
@str varchar (1024),--string to be split
@split varchar (10)--Separator symbol
)
returns int
As
Begin
DECLARE @location int
DECLARE @start int
DECLARE @length int

Set @str =ltrim (RTrim (@str))
Set @location =charindex (@split, @str)
Set @length =1
While @location <>0
Begin
Set @start = @location +1
Set @location =charindex (@split, @str, @start)
Set @length = @length +1
End
Return @length
End
Call Example: Select Dbo.get_strarraylength (' 78,1,2,3 ', ', ', ')
return value: 4

Home 1 2 last page