The T-SQL handles strings, splits strings like Arrays

Arrays are frequently used in routine programming. When using SQL to operate databases, you sometimes want to use arrays in SQL, such as splitting 1, 2, 3, 4, and 5 into arrays. Unfortunately, arrays are not supported in the T-SQL. However, there are still some alternatives. You can write two functions, get_strarraylength (get the length of the string-number of elements) and get_strarraystrofindex (split the string by the specified symbol and return the nth element of the specified index after the split ). With these two functions, we can easily process strings like 1, 2, 4, 5 in the T-SQL.

T-SQL on the string processing capabilities are relatively weak, such as I want to loop traversal like 1, 2, 3, 4, 5 such strings, if the use of arrays, traversal is very simple, but the T-SQL does not support the array, so it is troublesome to handle it. The following function implements string processing like an array.

1. Split the string by the specified symbol and return the number of elements after the split. The method is very simple, that is, to check the number of separators in the string, and then add one, which is the required result.
Create Function get_strarraylength
(
@ STR varchar (1024), -- string to be split
@ Split varchar (10) -- delimiter number
)
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

 

2. Split the string by the specified symbol and return the nth element of the specified index after the split, which is as convenient as an array.

Create Function get_strarraystrofindex
(
@ STR varchar (1024), -- string to be split
@ Split varchar (10), -- Separator
@ Index int -- obtains the nth element.
)
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
If @ location = 0 select @ location = Len (@ Str) + 1

-- There are two cases: 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.
Return substring (@ STR, @ start, @ location-@ start)
End

Call example: Select DBO. get_strarraystrofindex ('8, 9, 4', ',', 2)

Return Value: 9

 

3. Combine the above two functions to traverse the elements in the string like an array

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 @ next = @ next + 1
End

Call result:
1
2
3
4
5