SQL Server analog array "Go"

SQL Server emulated array

--sql is less capable of handling strings, such as I want to iterate through strings like 1,2,3,4,5, and if you use arrays, traversal is simple, but T-SQL does not support arrays, so it is cumbersome to deal with them. The function below implements a string that is treated like an array.
One, using 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)

Split the string by the specified symbol, return the number of elements after the split, the method is very simple, is to see how many separators exist in the string, and then add one, is the result of the request.
CREATE function Get_strarraylength
(
@str varchar (1024),--the 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 @[email protected]+1
Set @location =charindex (@split, @str, @start)
Set @[email protected]+1
End
Return @length
End
Invocation Example: SELECT dbo. Get_strarraylength (' 78,1,2,3 ', ', ')
return value: 4

Divide the string by the specified symbol, returning the first element of the specified index after the split, as easy as an array
CREATE function Get_strarraystrofindex
(
@str varchar (1024),--the string to be split
@split varchar (10),--Separator symbol
@index INT--Take the first few elements
)
Returns varchar (1024)
As
Begin
DECLARE @location int
DECLARE @start int
DECLARE @next int
DECLARE @seed int

Set @str =ltrim (RTrim (@str))
Set @start =1
Set @next =1
Set @seed =len (@split)

Set @location =charindex (@split, @str)
While @location <>0 and @index > @next
Begin
Set @[email Protected][email protected]
Set @location =charindex (@split, @str, @start)
Set @[email protected]+1
End
If @location =0 select @location =len (@str) +1
There are two cases here: 1, the string does not exist in the delimiter symbol 2, there is a delimiter in the string, after jumping out of the while loop, the @location is 0, the default is a string behind a separator symbol.

return substring (@str, @start, @[email protected])
End
Invocation Example: SELECT dbo. Get_strarraystrofindex (' 8,9,4 ', ', ', 2)
Return Value: 9

Three, combined with the top two functions, like an array to traverse the elements in the string

DECLARE @str varchar (50)
Set @str = ' 1,2,3,4,5 '
DECLARE @next int
Set @next =1
While @next <=dbo. Get_strarraylength (@str, ', ')
Begin
Print dbo. Get_strarraystrofindex (@str, ', ', @next)
Set @[email protected]+1
End

Call Result:
1
2
3
4
5

Transferred from: http://bbs.csdn.net/topics/290052887

SQL Server analog array "Go"