[Reprinted] The T-SQL handles strings like an array

-- ========================================================== ================
-- T-SQL on the string processing ability is relatively weak, such as loop traversal like 1, 2, 3, 4, 5 such strings, if the use of an array, 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 ) -- Separator
)
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   -- 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   @ 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 the while loop exists, @ 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  



-- ========================