How Do I Split a Delimited String in SQL Server Without Creating a Function?

Question

I'm working with a SQL Server database. I have a column which contains a delimited list, and I need to write a query which splits the values of the list into rows. From browsing StackOverflow and the rest of the web, I know this is a common problem. In fact, I found an extensive analysis here:

http://www.sommarskog.se/arrays-in-sql.html

Unfortunately, every solution I've seen on that site and elsewhere requires me to create a function. That isn't an option for me -- I lack the privileges required to use the CREATE command.

Without CREATE, I know I can use the PARSENAME function, something like this (Thanks to Nathan Bedford at How do I split a string so I can access item x?.):

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)

However, PARSENAME works only for lists of 4 items or fewer. My question, therefore, is this: How do I write a query to split a delimited string of more than 4 items without creating new objects in the database?

EDIT:

Thanks to everyone for the quick answers. I may have left out some important information -- I'm interacting with the database through an ODBC connection. In addition to CREATE statements, there seem to be other statements that don't work. For instance, I can't seem to use DECLARE in one statement to define a variable that will be used in another statement. As near as I can figure, I have to put everything into a single SELECT statement (although WITH also seems to work for declaring common tables). Unfortunately, all of the solutions suggested so far seem to require variable declarations outside the SELECT statement, and that isn't working. Please bear with me -- I'm learning as I go.

score 17 · Answer 1 · edited Dec 13 '12 at 17:52

17

A version using XML.

declare @S varchar(100) = 'Hello John Smith'

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace(@S, ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

Using a table instead Replace @T with what ever table you are using.

-- Test table
declare @T table (ID int, Col varchar(100))
insert into @T values (1, 'Hello John Smith')
insert into @T values (2, 'xxx yyy zzz')

select 
  T.ID,
  n.r.value('.', 'varchar(50)')
from @T as T
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)

Splitting the string 'Hello John Smith' without using a variable

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace('Hello John Smith', ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

edited Dec 13 '12 at 17:52

Community

1
1

answered Apr 19 '11 at 21:16

Mikael Eriksson

136,425
22
210
281

BTW first one using XML has very bad perfomance with rather large arrays – Johnny_D Oct 17 '13 at 08:17
@Johnny_D Could be improved by not casting in the query and use a xml variable instead. Will not be as fast as using a numberstable. [SQL Fiddle](http://sqlfiddle.com/#!6/d41d8/9409) – Mikael Eriksson Oct 17 '13 at 09:04
I'm not good at it, would be grateful if you show how to do it without casting? – Johnny_D Oct 17 '13 at 09:07
@Johnny_D Just follow the link to SQL Fiddle in my last comment. – Mikael Eriksson Oct 17 '13 at 11:05
@MikaelEriksson Could you plz update SQL fiddle link if available. If not, never mind thanks for answer anyway – COLD ICE Jan 19 '15 at 10:47

score 7 · Answer 2 · answered Apr 19 '11 at 21:09

example using the built in master..spt_values table

DECLARE @String VARCHAR(1000)
    SELECT @String ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5'

    SELECT SUBSTRING(',' + @String + ',', Number + 1,
    CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1)AS VALUE
    FROM master..spt_values
    WHERE Type = 'P'
    AND Number <= LEN(',' + @String + ',') - 1
    AND SUBSTRING(',' + @String + ',', Number, 1) = ','
    GO

See here for more: Split A String By Using A Number Table

score 4 · Answer 3 · answered Apr 19 '11 at 22:46

You can use recursive CTE to progressively extract one item

Sample table

create table aTable(a int identity primary key, b int, c varchar(100))
insert aTable values (1, 'this is a test string')
insert aTable values (1, 'this is another test string')
insert aTable values (2, 'here is a test string to put the others to shame')
insert aTable values (4, '')
insert aTable values (5, null)
insert aTable values (5, '-the end- ')

The query

;with tmp(a, b, c, position, single) as (
select a, b,
    STUFF(c, 1, CHARINDEX(' ', c + ' .'), ''),
    1,
    convert(nvarchar(max),left(c, CHARINDEX(' ', c + ' .') -1))
from aTable
union all
select a, b,
    STUFF(c, 1, CHARINDEX(' ', c + ' .'), ''),
    position+1,
    convert(nvarchar(max),left(c, CHARINDEX(' ', c + ' .') -1))
from tmp
where c > '')
select a, b, single, position
from tmp
order by a, position

Notes:

The delimiter here is a single space, which is what is searched for by CHARINDEX. The dot in ' .' is required because SQL Server normally does not see trailing spaces as significant.
ALL columns of the original table can be preserved in the CTE, just add them. Here I show an example of 2 columns a and b preserved in the output, with the column c being split into single and an extra column to indicate the position.

I swear this is dark magic, but it works. :) (And unlike many of the other answers, actually satisfies the OP's requirement to not use functions or anything else that requires db rights that many of us don't have.) — Martha, Apr 22 '22 at 20:04

score 3 · Answer 4 · edited Nov 13 '18 at 22:54

For late comers to this question, the article at http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings provides an excellent analysis of performance for various options. Some of the options considered include (copied from the site for reference):

CLR

.Net code at http://dataeducation.com/sqlclr-string-splitting-part-2-even-faster-even-more-scalable/.

CREATE ASSEMBLY CLRUtilities FROM 'c:\DLLs\CLRUtilities.dll' 
  WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.SplitStrings_CLR
(
   @List      NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE ( Item NVARCHAR(4000) )
EXTERNAL NAME CLRUtilities.UserDefinedFunctions.SplitString_Multi;
GO

XML

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

CTE

CREATE FUNCTION dbo.SplitStrings_CTE
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   RETURN;
END
GO

Function

CREATE FUNCTION dbo.SplitStrings_Moden
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;

It turns out, the best performance comes from using the CLR function while the XML solution also does well. In nearly all cases, using a numbers table (the approach was not duplicated above) lead to the worst performance.

Be careful. As I tried to warn the author of that article, he uses duplicated duplicate data for all 10,000 rows of test data. That's something that won't usually occur in real life and it skews the test results in favor of the actually terribly slothful XML splitter which, BTW, does not handle the XML special characters. — Jeff Moden, Dec 18 '16 at 15:13

SeeCoolGuy · Answer 5 · 2017-08-09T14:53:00.343

using a UDF makes the most sense it's flexible for all projects, the one I generally use is at my blog,

http://sqlthis.blogspot.com/2005/02/list-to-table.html

because it's written to take your input string and a delimiter, it can be any single character for a delimiter. I wrote the one at the link above, but then afterwards I found many sites publishing a similar solution, so parts may have been inspired from forums that I am a member of...

it works, and hopefully it will work for you as well.

edit 2017-08-09 as suggested, I've cloned the code block below. thanks!

CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Field1 VARCHAR(6))
  AS
  BEGIN
  --By: Francisco Tapia
  --Date: 2/1/2005
  --Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql
  -- Instead you can join the temp table or use it in your where clause if a field is IN the subquery
       DECLARE @FieldText as VarChar(6)

       IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
       SET @HList = @HList + @Delimiter

       WHILE CHARINDEX(@Delimiter, @HList) > 0
       BEGIN
            IF CHARINDEX(@Delimiter, @HList) > 0
            BEGIN
                 SELECT @FieldText =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
            END
       ELSE 
       BEGIN
            SELECT @FieldText = RTRIM(LTRIM(@HList))
       END
       --Insert into Variable Table
       INSERT INTO @ListTable(Field1)
       SELECT RTRIM(LTRIM(@FieldText))
       --Remove Item from list
       SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) -
            CHARINDEX(@Delimiter, @HList))
       END 
       RETURN
  END

score 0 · Answer 6 · answered Sep 15 '17 at 08:14

 DECLARE @cols  AS NVARCHAR(max), 
        @Val   VARCHAR(100)='Hi- Hello break this-Wall', 
        @Deli  VARCHAR(50)='-', 
        @query AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT ',' + Quotename(id) 
                      FROM   (SELECT stringpieceid AS ID, 
                                     stringpiece 
                              FROM 
                     [Utility].[dbo].[Splitstringtotable](@Val, @Deli)) 
                             X 
                      FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, 
               '') 

SELECT @query = 
'SELECT * FROM (SELECT StringPieceID as ID,StringPiece from [Utility].[dbo].[SplitStringToTable](''' 
         + @Val + ''',''' + @Deli + '''))X PIVOT  (     MAX(StringPiece)     for [ID] in (' + @cols 
         + ') ) P' 

PRINT @query 

EXEC Sp_executesql 
  @query

if it has delimeter then it break it into 3 parts and if not then Only in single part — Vikram Singh, Sep 15 '17 at 08:16

score 0 · Answer 7 · answered May 11 '18 at 11:19

USE TRIAL
GO

CREATE TABLE DETAILS
(
ID INT,
NAME VARCHAR(50),
ADDRESS VARCHAR(50)
)

INSERT INTO DETAILS
VALUES (100, 'POPE-JOHN-PAUL','VATICAN CIT|ROME|ITALY')
,(240, 'SIR-PAUL-McARTNEY','NEWYORK CITY|NEWYORK|USA')
,(460,'BARRACK-HUSSEIN-OBAMA','WHITE HOUSE|WASHINGTON|USA')
,(700, 'PRESIDENT-VLADAMIR-PUTIN','RED SQUARE|MOSCOW|RUSSIA')
,(950, 'NARENDRA-DAMODARDAS-MODI','10 JANPATH|NEW DELHI|INDIA')

select [ID]
,[NAME]
,[ADDRESS]
,REPLACE(LEFT(NAME, CHARINDEX('-', NAME)),'-',' ') as First_Name
,CASE 
WHEN CHARINDEX('-',REVERSE(NAME))+ CHARINDEX('-',NAME) < LEN(NAME)
THEN  SUBSTRING(NAME, CHARINDEX('-', (NAME)) + 1, LEN(NAME) - CHARINDEX('-' 
, REVERSE(NAME)) - CHARINDEX('-', NAME))
  ELSE 'NULL
  END AS Middle_Name
,REPLACE(REVERSE( SUBSTRING( REVERSE(NAME), 1, CHARINDEX('- 
',REVERSE(NAME)))), '-','') AS Last_Name 
,REPLACE(LEFT(ADDRESS, CHARINDEX('|', ADDRESS)),'|',' ') AS Locality
,CASE 
    WHEN CHARINDEX('|',REVERSE(ADDRESS))+ CHARINDEX('|',ADDRESS) < 
  LEN(ADDRESS) 
 THEN SUBSTRING(ADDRESS, CHARINDEX('|', (ADDRESS))+1, LEN(ADDRESS)- 
CHARINDEX('|', REVERSE(ADDRESS))-CHARINDEX('|',ADDRESS))
  ELSE 'Null' 
END AS STATE
,REPLACE(REVERSE(SUBSTRING(REVERSE(ADDRESS),1 
,CHARINDEX('|',REVERSE(ADDRESS)))),'|','') AS Country
 FROM DETAILS
  SELECT CHARINDEX('-', REVERSE(NAME)) AS LAST,CHARINDEX('-',NAME)AS FIRST, 
 LEN(NAME) AS LENGTH
 FROM DETAILS

-- LET ME KNOW IF YOU HAVE DOUBTS UNDERSTANDING THE CODE

score 0 · Answer 8 · answered Apr 19 '11 at 21:09

0

Have you looked at the numbers table?

http://www.sqlservercentral.com/articles/T-SQL/62867/

http://bradsruminations.blogspot.com/2010/08/integer-list-splitting-sql-fable.html

answered Apr 19 '11 at 21:09

Sankar Reddy

1,499
9
10

score 0 · Answer 9 · answered Apr 19 '11 at 21:11

I would just take one of the many functions that creates a table and instead of having it return the value put it in a table variable. Then use the table variable. Here is one example that returns a table.

http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx

score -1 · Answer 10 · answered Aug 02 '18 at 02:58

-1

Here's a user-defined parsing function that enables SQL Server that also performs similarly to the VB "Split" function. Designed for interactive leveraging; for example, to parse data within a Stored Procedure called from an external API.

https://gallery.technet.microsoft.com/scriptcenter/User-def-function-enabling-98561cce

answered Aug 02 '18 at 02:58

Big D

129
1
4

1

OP specifically stated that he/she wants to split text without user functions. – caiosm1005 Jul 02 '19 at 01:40

score -2 · Answer 11 · answered May 09 '17 at 18:56

-2

For sql Server >= 2016 you can use string_split as below:

SELECT * FROM string_split('Hello John Smith', ' ')

Output

+-------+
| value |
+-------+
| Hello |
| John  |
| Smith |
+-------+

answered May 09 '17 at 18:56

Kannan Kandasamy

13,405
3
25
38

How Do I Split a Delimited String in SQL Server Without Creating a Function?

11 Answers11

Linked

Related