Common functions for split string splitting in sqlserver

SQL Server is commonly used to split string functions.

If object_id (n'fn _ Split ') is not null
Drop function fn_split
Go
Create Function DBO. fn_split
(
@ Inputstr varchar (8000 ),
@ Seprator varchar (10 ),
@ P int -- the number of data records to be retrieved, starting from 0. If you want to return the split array list, clear and delete it -- #
)
Returns @ temp table (A varchar (200 ))
As

Begin
Declare @ I int
Declare @ n int -- record the number of cycles
Set @ inputstr = rtrim (ltrim (@ inputstr ))
Set @ I = charindex (@ seprator, @ inputstr)
Set @ n = 0 --##

While @ I> = 1
Begin
If @ P = @ n --##
Begin
Insert @ temp values (left (@ inputstr, @ I-1 ))
End
Set @ inputstr = substring (@ inputstr, @ I + 1, Len (@ inputstr)-@ I)
Set @ I = charindex (@ seprator, @ inputstr)
Set @ n = @ n + 1 --##
End

If @ inputstr <> ''-- last digit
If @ P = @ n --##
Insert @ temp values (@ inputstr)
Return
End
Go

 

 

 

Method 1
Create Function uf_strsplit '1. 1.2.50 ','.'
(@ Origstr varchar (7000), -- string to be split
@ Markstr varchar (100) -- split tag, such ','
Returns @ splittable table
(
Str_id varchar (4000) not null, -- ID
String varchar (2000) not null -- split string
)
As
Begin
Declare @ strlen int, @ postion int, @ start int, @ sublen int,
@ Tempstr varchar (200), @ tempid int
Select @ strlen = Len (@ origstr), @ start = 1, @ sublen = 0, @ postion = 1,
@ Tempstr = '', @ tempid = 0
If (right (@ origstr, 1) <> @ markstr)
Begin
Set @ origstr = @ origstr + @ markstr
End
While (@ postion <= @ strlen) and (@ postion! = 0 ))
Begin
If (charindex (@ markstr, @ origstr, @ postion )! = 0)
Begin
Set @ sublen = charindex (@ markstr, @ origstr, @ postion)-@ postion;
End
Else
Begin
Set @ sublen = @ strlen-@ postion + 1;
End
If (@ postion <= @ strlen)
Begin
Set @ tempid = @ tempid + 1;
Set @ tempstr = substring (@ origstr, @ postion, @ sublen );
Insert into @ splittable (str_id, string)
Values (@ tempid, @ tempstr)
If (charindex (@ markstr, @ origstr, @ postion )! = 0)
Begin
Set @ postion = charindex (@ markstr, @ origstr, @ postion) + 1
End
Else
Begin
Set @ postion = @ postion + 1
End
End
End
Return
End
Method 2
Create Function DBO. fn_split
(
@ Inputstr varchar (8000 ),
@ Seprator varchar (10)
)
Returns @ temp table (A varchar (200 ))
As

Begin
Declare @ I int

Set @ inputstr = rtrim (ltrim (@ inputstr ))
Set @ I = charindex (@ seprator, @ inputstr)

While @ I> = 1
Begin
Insert @ temp values (left (@ inputstr, @ I-1 ))

Set @ inputstr = substring (@ inputstr, @ I + 1, Len (@ inputstr)-@ I)
Set @ I = charindex (@ seprator, @ inputstr)
End

If @ inputstr <>''
Insert @ temp values (@ inputstr)

Return
End
Go

-- Call

Declare @ s varchar (1000)

Set @ s = 'sa1, SB1, scs'

Select * From DBO. fn_split (@ s ,',')

Drop function DBO. fn_splitsqlserver implements the split string Function