Code
Create Function DBO. splitstring
(
@ Expression Nvarchar ( 4000 ), -- String to be split
@ Delimiter Nvarchar ( 100 ), -- Shard symbol
@ N Int -- To obtain the split return string location
)
Returns Nvarchar ( 4000 )
As
Begin
Declare @ P Int
Set @ P = Charindex ( @ Delimiter , @ Expression )
If @ P > 0
Begin
Set @ P = @ P + Len ( @ Delimiter ) - 1
End
Declare @ I Int
Set @ I = 1
While @ I < @ N
Begin
Set @ I = @ I + 1
Set @ Expression = Substring ( @ Expression , @ P + 1 , Len ( @ Expression ) - @ P )
Set @ P = Charindex ( @ Delimiter , @ Expression )
If @ P > 0
Begin
Set @ P = @ P + Len ( @ Delimiter ) - 1
End
Else
Begin
Break
End
End
Declare @ S Nvarchar ( 1000 )
If @ P = 0 And @ I = @ N
Begin
Set @ S = @ Expression
End
Else
If @ I = @ N
Begin
Set @ S = Substring ( @ Expression , 1 , @ P - Len ( @ Delimiter ))
End
Return @ S
End
Usage:
Code
Declare @ String Nvarchar ( 50 )
Set @ String = ' Implement split in | SQL Server | function | '
Declare @ Split Nvarchar ( 4 )
Set @ Split = ' | '
Select DBO. splitstring ( @ String , @ Split , 1 ) -- In
Select DBO. splitstring ( @ String , @ Split , 2 ) -- SQL Server
Select DBO. splitstring ( @ String , @ Split , 3 ) -- Split
Select DBO. splitstring ( @ String , @ Split , 4 ) -- Functional
Select DBO. splitstring ( @ String , @ Split , 5 ) -- Function
Select DBO. splitstring ( @ String , @ Split , 6 ) -- Null
