SQL Server string splitting function

Source: Internet
Author: User
Tags rtrim

First, to divide the string by the specified symbol, return the number of elements after the split, the method is very simple, is to see how many separators exist in the string, and then add one, is the result of the request.

CREATE function Get_strarraylength
(
@str varchar (1024),--the string to be split
@split varchar (10)--Separator symbol
)
returns int
As
Begin
DECLARE @location int
DECLARE @start int
DECLARE @length int

Set @str =ltrim (RTrim (@str))
Set @location =charindex (@split, @str)
Set @length =1
While @location <>0
Begin
Set @[email protected]+1
Set @location =charindex (@split, @str, @start)
Set @[email protected]+1
End
Return @length
End
Invocation Example: SELECT dbo. Get_strarraylength (' 78,1,2,3 ', ', ')
return value: 4

Split the string by the specified symbol, returning the first element of the specified index after the split, as easy as an array

CREATE function Get_strarraystrofindex
(
@str varchar (1024),--the string to be split
@split varchar (10),--Separator symbol
@index INT--Take the first few elements
)
Returns varchar (1024)
As
Begin
DECLARE @location int
DECLARE @start int
DECLARE @next int
DECLARE @seed int

Set @str =ltrim (RTrim (@str))
Set @start =1
Set @next =1
Set @seed =len (@split)

Set @location =charindex (@split, @str)
While @location <>0 and @index > @next
Begin
Set @[email Protected][email protected]
Set @location =charindex (@split, @str, @start)
Set @[email protected]+1
End
If @location =0 select @location =len (@str) +1
There are two cases here: 1, the string does not exist in the delimiter symbol 2, there is a delimiter in the string, after jumping out of the while loop, the @location is 0, the default is a string behind a separator symbol.

return substring (@str, @start, @[email protected])
End
Invocation Example: SELECT dbo. Get_strarraystrofindex (' 8,9,4 ', ', ', 2)
Return Value: 9

Three, combined with the top two functions, like an array to traverse the elements in the string

Create function F_splitstr (@SourceSql varchar (8000), @StrSeprate varchar (100))
Returns @temp table (F1 varchar (100))
As
Begin
DECLARE @ch as varchar (100)
Set @[email Protected][email protected]
while (@SourceSql <> ")
Begin
Set @ch =left (@SourceSql, charindex (', ', @SourceSql, 1)-1)
Insert @temp VALUES (@ch)
Set @SourceSql =stuff (@SourceSql, 1,charindex (', ', @SourceSql, 1), ")
End
Return
End


----Call
SELECT * from Dbo.f_splitstr (' 1,2,3,4 ', ', ')
--Results:
1
2
3
4

--Method 0: Dynamic SQL method
DECLARE @s varchar (+), @sql varchar (1000)
Set @s= ' 1,2,3,4,5,6,7,8,9,10 '
Set @sql = ' Select col= ' + replace (@s, ', ', ' union ALL SELECT ') + '
PRINT @sql
EXEC (@sql)

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ F_SPLITSTR] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [F_splitstr]
GO
--Method 1: Cyclic interception method
CREATE FUNCTION F_splitstr (
@s varchar (8000),--string to be split
@split varchar (10)--Data delimiter
) RETURNS @re TABLE (col varchar (100))
As
BEGIN
DECLARE @splitlen int
SET @splitlen =len (@split + ' a ')-2
While CHARINDEX (@split, @s) >0
BEGIN
INSERT @re VALUES (left (@s,charindex (@split, @s)-1))
SET @s=stuff (@s,1,charindex (@split, @s) [email protected], ')
END
INSERT @re VALUES (@s)
RETURN
END
GO

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ F_SPLITSTR] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [F_splitstr]
GO
--Method 2: Using the Temporary partition auxiliary table method
CREATE FUNCTION F_splitstr (
@s varchar (8000),--string to be split
@split varchar (10)--Data delimiter
) RETURNS @re TABLE (col varchar (100))
As
BEGIN
--Create a secondary table for split processing (only table variables can be manipulated in user-defined functions)
DECLARE @t TABLE (ID int identity,b bit)
INSERT @t (b) SELECT TOP 8000 0 from syscolumns a,syscolumns b

INSERT @re SELECT SUBSTRING (@s,id,charindex (@split, @[email protected],id)-id)
From @t
WHERE Id<=len (@s+ ' a ')
and CHARINDEX (@split, @[email protected],id) =id
RETURN
END
GO

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ F_SPLITSTR] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [F_splitstr]
GO
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ TB_SPLITSTR] ') and OBJECTPROPERTY (Id,n ' isusertable ') =1)
drop table [dbo]. [Tb_splitstr]
GO
--Method 3: Using the Permanent partition auxiliary table method
--String splitting auxiliary table
SELECT TOP 8000 id=identity (int,1,1) into Dbo.tb_splitstr
From syscolumns A,syscolumns b
GO
--string spin-off processing function
CREATE FUNCTION F_splitstr (
@s varchar (8000),--string to be split
@split varchar (10)--Data delimiter
) RETURNS TABLE
As
RETURN (
SELECT Col=cast (SUBSTRING (@s,id,charindex (@split, @[email protected],id)-id) as varchar (100))
From Tb_splitstr
WHERE Id<=len (@s+ ' a ')
and CHARINDEX (@split, @[email protected],id) =id)
GO

--Method 4: Use SQL server2005 outer APPLY

CREATE FUNCTION [dbo]. [Ufn_splitstringtotable]
(
@str VARCHAR (MAX),
@split VARCHAR (10)
)
RETURNS TABLE
As
RETURN
(SELECT b.id
From (SELECT [value] = CONVERT (XML, ' <v> ' + REPLACE (@str, @split, ' </v><v> ')
+ ' </v> ')
) A
OUTER APPLY (SELECT id = n.v.value ('. ', ' varchar (100) ')
From A.[value].nodes ('/V ') N (v)
) B
)

SQL Server string splitting function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.