SQL string split function

SQL string split function

Splits a string by a specified character.

Idea: 1. Calculate the index of the specified delimiter in the string,

2. Split the string.

The SQL code is as follows:

Declare @ strorder nvarchar (200)
Declare @ ismulorder int
Declare @ firstOrder nvarchar (200)
Declare @ secondOrder nvarchar (200)

Set @ strorder = 'lastmodifydate Desc, OurPrice Asc'

Select CHARINDEX (',', @ strorder, 0)
-- Select LEN (@ strorder)
-- Select SUBSTRING (@ strorder, 0, CHARINDEX (',', @ strorder, 0 ))
-- Select SUBSTRING (@ strorder, CHARINDEX (',', @ strorder, 0) + 1, LEN (@ strorder ))
Set @ ismulorder = CHARINDEX (',', @ strorder, 0)
If @ ismulorder> 0
Begin
Set @ firstOrder = 'p. '+ SUBSTRING (@ strorder, 0, CHARINDEX (', ', @ strorder, 0 ))
Set @ secondOrder = 'p. '+ SUBSTRING (@ strorder, CHARINDEX (', ', @ strorder, 0) + 1, LEN (@ strorder ))
Set @ strorder = @ firstOrder + ',' + @ secondOrder
End

Select @ strorder

Use the split function

Create function [dbo]. [split]
(
@ SourceSql varchar (8000 ),
@ StrSeprate varchar (10)
)
Returns @ temp table (F1. varchar (100 ))
As
Begin
Declare @ I int
Set @ SourceSql = rtrim (ltrim (@ SourceSql ))
Set @ I = charindex (@ StrSeprate, @ SourceSql)
While @ I> = 1
Begin
If len (left (@sourcesql, @ i-1)> 0
Begin
Insert @ temp values (left (@ SourceSql, @ i-1 ))
End
Set @ SourceSql = substring (@ SourceSql, @ I + 1, len (@ SourceSql)-@ I)
Set @ I = charindex (@ StrSeprate, @ SourceSql)
End
If @ SourceSql <>''
Insert @ temp values (@ SourceSql)
Return
End
 
Select * from split (', 777, 11, 888, 88, 1122,888, 88,77, 00, 00 ',',')