-- Splits a string by the specified symbol and returns the number of split elements.
Create Function get_strarraylength (
@ STR varchar (1024), -- split string
@ Split varchar (10) -- delimiter
)
Returns int
As
Begin
Declare @ location int
Declare @ start int
Declare @ length int
Set @ STR = ltrim (rtrim (@ Str ))
Set @ location = charindex (@ split, @ Str) -- returns the starting position of the specified expression in the string.
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 ',',')
-- Splits the string by the specified symbol and returns the nth element of the specified index after the split.
Create Function get_strarraystrofindex (
@ STR varchar (1024), -- string to be split
@ Split varchar (10), -- delimiter
@ Index int -- obtains the nth element.
)
Returns varchar (1024)
As
Begin
Declare @ location int -- positioning point
Declare @ start int -- start position
Declare @ next int -- next position
Declare @ seed int -- delimiter Length
Set @ STR = ltrim (rtrim (@ Str ))
Set @ start = 1
Set @ next = 1
Set @ seed = Len (@ split) -- get the separator Length
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
-- There are two cases: 1. the string does not have a separator.
-- 2. There is a separator in the string. After the while loop is exceeded, @ 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 example: Select DBO. get_strarraystrofindex ('8, 9, 4', ',', 4)
