The T-SQL processes strings like arrays, split strings

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.

2. Create FunctionGet_strarraylength
4. (
6. @ STR varchar (1024 ),-- String to be split
8. @ Split varchar (10)-- Separator
10. )
12. Returns int
14. As
16. Begin
18. Declare@ Location int
20. Declare@StartInt
22. Declare@LengthInt
26. Set@ STR =Ltrim(Rtrim(@ Str ))
28. Set@ Location = charindex (@ split, @ Str)
30. Set@Length= 1
32. While@ Location <> 0
34. Begin
36. Set@Start= @ Location + 1
38. Set@ Location = charindex (@ split, @ STR ,@Start)
40. Set@Length= @Length+ 1
42. End
44. Return@Length
46. 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.

2. Create FunctionGet_strarraystrofindex
4. (
6. @ STR varchar (1024 ),-- String to be split
8. @ Split varchar (10 ),-- Separator
10. @IndexInt-- Take the nth Element
12. )
14. Returns varchar (1024)
16. As
18. Begin
20. Declare@ Location int
22. Declare@StartInt
24. Declare@ Next int
26. Declare@ Seed int
30. Set@ STR =Ltrim(Rtrim(@ Str ))
32. Set@Start= 1
34. Set@ Next = 1
36. Set@ Seed = Len (@ split)
40. Set@ Location = charindex (@ split, @ Str)
42. While@ Location <> 0And@Index> @ Next
44. Begin
46. Set@Start= @ Location + @ Seed
48. Set@ Location = charindex (@ split, @ STR ,@Start)
50. Set@ Next = @ next + 1
52. End
54. If@ Location = 0Select@ Location = Len (@ Str) + 1
56. -- 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.
60. ReturnSubstring (@ STR ,@Start, @ Location -@Start)
62. 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

1. Declare@ STR varchar (50)
2. Set@ STR ='1, 2, 3, 4, 5'
3. Declare@ Next int
4. Set@ Next = 1
5. While@ Next <= DBO. get_strarraylength (@ STR,',')
6. Begin
7. Print DBO. get_strarraystrofindex (@ STR,',', @ Next)
8. Set@ Next = @ next + 1
9. End

Call result:
1
2
3
4
5

http: // www.616.cn HTTP: // game.616.cn
flex web board game, video conversion software, crawlers are welcome to buy them! Slide the internet!