SQL Server: Split operation

How to split a string in SQL Server.

Example:

Input string: stack over flow

Result:

stack
over
flow

if you can't use table value parameters, see: "Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters" by Erland Sommarskog , then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

You need to create a split function. This is how a split function can be used:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(' ','stack over flow')

OUTPUT:

ListValue
-------------------
stack
over
flow

(3 row(s) affected)

A common set-based solution to this kind of problem is to use a numbers table.

The following solution uses a simple recursive CTE to generate the numbers table on the fly - if you need to work with longer strings, this should be replaced with a static numbers table.

DECLARE @vch_string varchar(max)
DECLARE @chr_delim char(1)
SET @chr_delim = ' '
SET @vch_string = 'stack over flow'

;WITH nums_cte
AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT n+1 FROM nums_cte
  WHERE n < len(@vch_string)
)
SELECT n - LEN(REPLACE(LEFT(s,n),@chr_delim,'')) + 1 AS pos
       ,SUBSTRING(s,n,CHARINDEX(@chr_delim, s + @chr_delim,n) -n) as ELEMENT
FROM (SELECT @vch_string as s) AS D
JOIN nums_cte
ON n <= LEN(s)
AND SUBSTRING(@chr_delim + s,n,1) = @chr_delim
OPTION (MAXRECURSION 0);

I know this question was for SQL Server 2008 but things evolve so starting with SQL Server 2016 you can do this

DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')

SQL Server: Split operation

Related

Recent Posts