Original SQL table-valued functions: Converting comma-delimited strings into tabular data

A very common requirement in our daily development process is to separate a string of characters separated by commas or/or other symbols into a single table data.

In the front we introduced the [original]sql to convert the comma-separated contents of the field stored in the table into a list form, of course, it is possible to change it in this way, but I have the other way I have been used before, also record it.

In accordance with the Convention, we first look at the effect.

Call: SELECT Id from [fn_stringsplittotable] (' 1,12,36,65,58,56,df ', ', ')

The specific functions are as follows:

-- =============================================
--Call: SELECT Id from [fn_stringsplittotable] (' 1,12,36,65,58,56,df ', ', ')
--Create Date: <create date,,>
--Description: Converts a comma-delimited string into tabular data
-- =============================================
CREATE FUNCTION [dbo]. [Fn_stringsplittotable]
(
@StringX varchar (MAX),
@Split nvarchar (10)
)
RETURNS
@IdsTable TABLE
(
[Id] nvarchar (200)
)
As
BEGIN
DECLARE @Index int
DECLARE @LenIndex int
SET @LenIndex =len (@Split)
SET @Index =charindex (@Split, @StringX, 1)
while (@Index >=1)
BEGIN
IF (Left (@StringX, @Index-1) <> ")
INSERT into @IdsTable SELECT LTRIM (RTRIM (left (@StringX, @Index-1))
SET @StringX =right (@StringX, LEN (@StringX) [email protected] @LenIndex + 1)
SET @Index =charindex (@Split, @StringX, 1)
END
IF (@StringX <> ") INSERT into @IdsTable SELECT LTRIM (RTRIM (@StringX))

RETURN
END

The above calls are separated by commas, and if your string is separated by a/number, modify the calling string directly.

--Call: SELECT Id from [fn_stringsplittotable] (' 1/12/36/65/58/56/df ', '/')

if friends think that may be used later, welcome reprint, of course, can also recommend, thank you.

PS: This blog post is written using Windows Live Writer 2012, and the format effect may not be good.

Original SQL table-valued functions: Converting comma-delimited strings into tabular data