CODEWITHSUNDEEP
sql-server
Todd
Todd

Reputation: 339

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

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.

Upvotes: 17

Views: 95220

Answers (11)

Big D
Big D

Reputation: 129

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

Upvotes: -1

URMIL PREMAL SHAH
URMIL PREMAL SHAH

Reputation: 17

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

Upvotes: 0

Vikram Singh
Vikram Singh

Reputation: 124

 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

Upvotes: 0

SeeCoolGuy
SeeCoolGuy

Reputation: 81

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

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

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

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

Output

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

Upvotes: -2

erdomke
erdomke

Reputation: 5245

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.

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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)

Upvotes: 17

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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.

Upvotes: 4

Hogan
Hogan

Reputation: 70523

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

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

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

Upvotes: 7

Sankar Reddy
Sankar Reddy

Reputation: 1499

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

Upvotes: 0

Related Questions