SQL SERVER delimiter

1. Use the specified string to separate the elements. The number of elements after the split is returned.
Create function Get_StrLength
(
@ Str varchar (1024 ),
@ Split varchar (10)
)
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 method: select dbo. Get_StrLength ('7, 5, 6, 7, a, f, D ',',')

2. Split the string according to the specified symbol, and return the nth element of the specified index after the split, just like the Array

Create function Get_StrOfIndex
(
@ Str varchar (1024 ),
@ Split varchar (10 ),
@ Index int
)
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 @ start = @ location + @ seed
Set @ location = charindex (@ split, @ str, @ start)
Set @ next = @ next + 1
End
-- Note: There are two situations: 1. There is no Separator in the string. 2. There is a separator in the string. After jumping out of the while LOOP, @ location is 0, by default, there is a separator behind the string.

If @ location = 0
Select @ location = len (@ str) + 1
Return substring (@ str, @ start, @ location-@ start)
End
Call method: select dbo. Get_StrOfIndex ('8, 9, 3, 3, 4, 5', ',', 26)
3. combine the two functions above to return the split element.
Create function f_splitstr
(
@ SourceSql varchar (8000 ),
@ StrSeprate varchar (100)
)
Returns @ temp table (F1. varchar (100 ))
As
Begin
Declare @ ch as varchar (100)
Set @ SourceSql = @ SourceSql + @ StrSeprate
While (@ SourceSql <> '')
Begin
Set
@ Ch = left (@ SourceSql, Charindex (',', @ SourceSql, 1)-1)
Insert @ temp values (@ ch)
Set @ SourceSql = stuff (@ SourceSql, 1, charindex (',', @ SourceSql, 1 ),'')
End
Return
End
Call method: select * from f_splitstr ('1, 2, 3, 4, 5, 6 ',',')

Transferred from:

Http://zhiwenweb.cn/jszx/sjkjs/mssql/201110/29418.html