Split string function in sqlserver

Commonly used.

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