Several commonly used SQL string splitting Functions

-- Split a string with the specified character and input a string to determine whether the passed string exists in the string array after the split
Create function SplitString (@ expression varchar (500), @ char varchar (10), @ str varchar (50 ))
Returns bit
As
Begin
-- If the input string is null, 0 is returned.
If (isnull (@ expression, '') = '')
Return 0
Declare @ beginIndex int, @ endIndex int -- truncates the start index and end index of a substring.
Declare @ Count int -- record the number of cycles
Set @ beginIndex = 1 -- Note: The index of a string in SQL starts from 1.
Set @ Count = 0
While (1 = 1)
Begin
Set @ Count = @ Count + 1
Set @ endIndex = charindex (@ char, @ expression, @ beginIndex)
-- If no separator is found, the entire string to be split is compared with the string to be judged.
If (@ endIndex = 0)
Begin
-- If the substring is intercepted for the first time
If (@ Count = 1)
Begin
If (@ expression = @ str)
Begin
Return 1
End
Begin
Return 0
End
End
Else -- if the substring is intercepted for the last time, the string following the last separator is obtained and compared with the string to be judged.
Begin
If (@ str = substring (@ expression, @ beginIndex, len (@ expression)-@ beginIndex + 1 ))
Begin
Return 1
End
Else
Begin
Return 0
End
End
End

-- If yes, 1 is returned.
If (@ str = substring (@ expression, @ beginIndex, @ endIndex-@ beginIndex ))
Begin
Return 1
End
Else -- if not matched, continue searching
Begin
Set @ beginIndex = @ endIndex + 1
End
End
Return 0
End

-- Split a string with the specified character and input an index. The string of the specified index after the split is returned (the index starts from 1)
Create function IndexOfArray (@ expression varchar (500), @ char varchar (10), @ Index int)
Returns varchar (200)
As
Begin
Declare @ beginIndex int, @ endIndex int -- truncates the start index and end index of a substring.
Declare @ Count int -- record the number of cycles
Set @ beginIndex = 1 -- Note: The index of a string in SQL starts from 1.
Set @ Count = 0
While (1 = 1)
Begin
Set @ Count = @ Count + 1
Set @ endIndex = charindex (@ char, @ expression, @ beginIndex)

-- If no separator is found, the substring from the last separator to the end of the string is truncated.
If (@ endIndex = 0)
Begin
Return substring (@ expression, @ beginIndex, len (@ expression)-@ beginIndex + 1)
End

-- If the index of the string intercepted this time is the specified index, the truncated string will be returned.
If (@ Count = @ Index)
Begin
Return substring (@ expression, @ beginIndex, @ endIndex-@ beginIndex)
End
Else -- otherwise continue
Begin
Set @ beginIndex = @ endIndex + 1
Continue
End
End
Return''
End
Go

-- Split a string with the specified character and input a string to determine the input string
-- Whether it is equal to the string of the specified index in the string array after the split
Create function SplitEquare (@ expression varchar (200), @ char varchar (10), @ Index int, @ str varchar (100 ))
Returns bit
As
Begin
Declare @ SplitStr varchar (100)
Set @ SplitStr = dbo. IndexOfArray (@ expression, @ char, @ Index)
If (@ SplitStr = @ str)
Begin
Return 1
End
Else
Begin
Return 0
End
Return 0
End
Go