If exists (select * From sys. objects where object_id = object_id (n' [DBO]. [get_strofindex] ') and type in (n'fn', n'if', n'tf', n'fs', n'ft '))
Drop function [DBO]. [get_strofindex]
Go
Create Function get_strofindex
(
@ STR varchar (1024 ),
@ Split varchar (10 ),
@ Index int
)
Returns varchar (1024)
As
Begin
Declare @ location int
Declare @ start int
Declare @ next int
Declare @ seed int
Set @ STR = ltrim (rtrim (@ Str ))
If substring (@ STR, 0, Len (@ Str)-1) <> @ split
Set @ STR + = @ split
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
-- Note: There are two situations: 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 number behind the string [fixed, last, with or without separators].
If @ location = 0
Select @ location = Len (@ Str) + 1
Return substring (@ STR, @ start, @ location-@ start)
End
Go
--- Call method: Select DBO. get_strofindex ('8, 9, 3, 3, 4, 5, ', 7)
--- No matter whether there is a separator at the end of the data
