By GateKiller

2008-08-05 18:15:47 8 Comments

Using SQL Server, how do I split a string so I can access item x?

Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"?


@jjxtra 2014-08-26 16:50:04

Almost all the other answers are replacing the string being split which wastes CPU cycles and performs unnecessary memory allocations.

I cover a much better way to do a string split here:

Here is the code:


-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1

SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)

WHILE @SplitEndPos > 0
    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
    INSERT @SplitStringTable (Value) VALUES (@SplitValue)
    SET @SplitStartPos = @SplitEndPos + 1
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)

SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)


-- You can select or join with the values in @SplitStringTable at this point.

@Aaron Bertrand 2013-11-12 17:16:08

Most of the solutions here use while loops or recursive CTEs. A set-based approach will be superior, I promise, if you can use a delimiter other than a space:

CREATE FUNCTION [dbo].[SplitString]
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
        RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM 
            SELECT n = Number, 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y

Sample usage:

SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
  WHERE idx = 3;



You could also add the idx you want as an argument to the function, but I'll leave that as an exercise to the reader.

You can't do this with just the native STRING_SPLIT function added in SQL Server 2016, because there is no guarantee that the output will be rendered in the order of the original list. In other words, if you pass in 3,6,1 the result will likely be in that order, but it could be 1,3,6. I have asked for the community's help in improving the built-in function here:

With enough qualitative feedback, they may actually consider making some of these enhancements:

More on split functions, why (and proof that) while loops and recursive CTEs don't scale, and better alternatives, if splitting strings coming from the application layer:

On SQL Server 2016 or above, though, you should look at STRING_SPLIT() and STRING_AGG():

@T-moty 2015-10-21 15:01:40

Best answer, IMHO. In some of other answers there is the issue of SQL recursion limit of 100, but not in this case. Very fast and very simple implementation. Where is the +2 button?

@Mikhail Boyarsky 2016-09-23 13:20:50

Thanks to Aaron; But can someone explain why if I pass varchar (not varchar(max)) as the argument this function returns empty list? like declare @list varchar = 'something'; select from dbo.SplitString(@list, ';');

@Aaron Bertrand 2016-09-23 20:08:29

@Mikhail Because varchar without length can either be varchar(30) or varchar(1) depending on context. Don't try to understand that problem - just don't use that syntax. Ever.

@wwmbes 2016-10-11 10:27:13

I tried this function verbatim with the usage: select * from DBO.SplitString('Hello John smith', ' '); and the output produced was: Value Hello ello llo lo o John ohn hn n smith mith ith th h

@Aaron Bertrand 2016-10-11 15:07:25

@wwmbes Try using a delimiter other than space. Many functions are going to have the issue that trailing spaces are dropped.

@wwmbes 2016-10-26 12:00:10

@AaronBertrand The original problem posted by GateKiller involves a space delimiter.

@Alasdair C-S 2019-10-22 13:20:44

The "len" function has an implict built-in "rtrim" which comes from the 80's when SQL only had char (not varchar) so try this: select len( ' ' ) as len_space, len( '* ' ) as len_star_space, len( ' *' ) as len_space_star, datalength( ' ' ) as datalength_space, datalength( '* ' ) as datalength_star_space, datalength( ' *' ) as datalength_space_star, case when '' = ' ' then 'yes' else 'no' end as nospace_equals_space Fix: replace "len" with "datalength" (but beware n(var)char)

@user1255933 2019-12-13 23:48:36

This splits the string. how do you retrieve a specific n-th element?

@Aaron Bertrand 2019-12-17 21:01:55

@user1255933 Addressed.

@user1255933 2019-12-19 19:43:30

@AaronBertrand Thanks very much, I can reproduce this.

@user1255933 2019-12-19 20:17:57

@AaronBertrand adding example for applying to a whole table: SELECT o.myfield, u.value from mytable o cross apply dbo.SplitString(o.myfield,';') u where u.idx = 4

@Michael 2020-02-26 23:58:38

This won't work if you don't have "CREATE FUNCTION" permission...

@Aaron Bertrand 2020-02-27 00:19:43

@Michael Yes, that’s true. You also wouldn’t have a table to select from if you didn’t have ALTER SCHEMA permission, and wouldn’t be able to select from it if you don’t have SELECT permission You could always ask someone to create the function for you. Or create it somewhere you can create it (even temporarily, say in tempdb). And on 2016+ you should be using STRING_SPLIT() and not a function you have to create yourself anyway.

@Michael 2020-02-27 03:41:44

@AaronBertrand I'm on 2016, but apparently not compatibility level >= 130 (the db admin wasn't sure and won't be able to look into it until later)

@Reversed Engineer 2020-04-08 14:30:59

@AaronBertrand "And on 2016+ you should be using STRING_SPLIT()" : I can't imagine any real-world use for STRING_SPLIT, since it does not return an index for the parts of the string that was split, and according to it's docs, "the order of the output may vary as the order is not guaranteed to match the order of the substrings in the input string".

@Reversed Engineer 2020-04-08 14:35:57

I like the way this Azure Feedback item puts it: 'STRING_SPLIT is not feature complete', and 'It’s a shame that this was submitted as just a “suggestion”. It should actually be listed as a “bug” because there’s only a comparatively small set of use cases where enumeration of the result set of elements is not important.'

@Aaron Bertrand 2020-04-08 16:44:32

@ReversedEngineer There are plenty of uses that don't (and shouldn't) care about the original order of the list. "Find all the customers in this list." Why does it matter if the list is 3,5,2,8 or 8,3,5,2? I think it's a rather obscure edge case that the requirement would be "Find all the customers in this list and render them in exactly this order." (I'm not arguing against fixing that, I'm just suggesting that my impression of the majority of uses for the function differs from yours. That is anecdotal evidence from the number of accepted answers showing functions without ordinal out.)

@Aaron Bertrand 2020-04-08 16:54:16

@ReversedEngineer Anyway, updated my answer with some of my thoughts around this. Also, as an aside, I was one of the ones who pushed for clarity in the docs, that output order may not match input order.

@Reversed Engineer 2020-04-09 09:32:02

@AaronBertrand "Also, as an aside, I was one of the ones who pushed for clarity in the docs, that output order may not match input order." - Thank you! I appreciate what you do to "press Microsoft for improvements", as well as help clear up this kind of vague "Microsoft documentation for Dummies". Also I see your point about there being cases when the order doesn't matter, in which case STRING_SPLIT is indeed a great solution.

@Eralper 2020-02-22 15:17:46

If you check the following SQL tutorial on splitting string using SQL, you will find a number of functions that can be used to split a given string on SQL Server

For example, SplitAndReturnNth UDF function can be used to split a text using a separator and return the Nth piece as the output of the function

select dbo.SplitAndReturnNth('Hello John Smith',' ',2)

enter image description here

@GBGOLC 2019-11-02 00:35:02

Using SQL Server 2016 and above. Use this code to TRIM strings, ignore NULL values and apply a row index in the correct order. It also works with a space delimiter:

DECLARE @STRING_VALUE NVARCHAR(MAX) = 'one, two,,three, four,     five'

SELECT ROW_NUMBER() OVER (ORDER BY R.[index]) [index], R.[value] FROM
        1 [index], NULLIF(TRIM([value]), '') [value] FROM STRING_SPLIT(@STRING_VALUE, ',') T
        NULLIF(TRIM([value]), '') IS NOT NULL
) R

@Shnugo 2016-07-08 20:41:19

This question is not about a string split approach, but about how to get the nth element.

All answers here are doing some kind of string splitting using recursion, CTEs, multiple CHARINDEX, REVERSE and PATINDEX, inventing functions, call for CLR methods, number tables, CROSS APPLYs ... Most answers cover many lines of code.

But - if you really want nothing more than an approach to get the nth element - this can be done as real one-liner, no UDF, not even a sub-select... And as an extra benefit: type safe

Get part 2 delimited by a space:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

Of course you can use variables for delimiter and position (use sql:column to retrieve the position directly from a query's value):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

If your string might include forbidden characters (especially one among &><), you still can do it this way. Just use FOR XML PATH on your string first to replace all forbidden characters with the fitting escape sequence implicitly.

It's a very special case if - additionally - your delimiter is the semicolon. In this case I replace the delimiter first to '#DLMT#', and replace this to the XML tags finally:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

UPDATE for SQL-Server 2016+

Regretfully the developers forgot to return the part's index with STRING_SPLIT. But, using SQL-Server 2016+, there is JSON_VALUE and OPENJSON.

With JSON_VALUE we can pass in the position as the index' array.

For OPENJSON the documentation states clearly:

When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.

A string like 1,2,3 needs nothing more than brackets: [1,2,3].
A string of words like this is an example needs to be ["this","is","an","example"].
These are very easy string operations. Just try it out:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

--See this for a position safe string-splitter (zero-based):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

In this post I tested various approaches and found, that OPENJSON is really fast. Even much faster than the famous "delimitedSplit8k()" method...

UPDATE 2 - Get the values type-safe

We can use an array within an array simply by using doubled [[]]. This allows for a typed WITH-clause:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
WITH(TheFirstFragment  VARCHAR(100) '$[0]'
    ,TheSecondFragment INT          '$[1]'
    ,TheThirdFragment  DATE         '$[2]') ValuesFromTheArray

@Salman A 2019-01-28 07:26:13

Re: if your string might include forbidden characters... you could simply wrap the substrings like so <x><![CDATA[x<&>x]]></x>.

@Shnugo 2019-01-28 07:32:30

@SalmanA, yeah ,CDATA-sections can deal with this too... But after the cast they are gone (changed to escaped text() implicitly). I do not like magic under the hood, so I'd prefer the (SELECT 'Text with <&>' AS [*] FOR XML PATH('')) - approach. This looks cleaner to me and happens anyway... (Some more about CDATA and XML).

@Dave Mason 2019-05-07 02:54:28

I realize this is a really old question, but starting with SQL Server 2016 there are functions for parsing JSON data that can be used to specifically address the OP's question--and without splitting strings or resorting to a user-defined function. To access an item at a particular index of a delimited string, use the JSON_VALUE function. Properly formatted JSON data is required, however: strings must be enclosed in double quotes " and the delimiter must be a comma ,, with the entire string enclosed in square brackets [].

DECLARE @SampleString NVARCHAR(MAX) = '"Hello John Smith"';
--Format as JSON data.
SET @SampleString = '[' + REPLACE(@SampleString, ' ', '","') + ']';
    JSON_VALUE(@SampleString, '$[0]') AS Element1Value,
    JSON_VALUE(@SampleString, '$[1]') AS Element2Value,
    JSON_VALUE(@SampleString, '$[2]') AS Element3Value;


Element1Value         Element2Value       Element3Value
--------------------- ------------------- ------------------------------
Hello                 John                Smith

(1 row affected)

@Dave Mason 2020-04-19 18:08:46

Downvote without a comment. 🙄

@Salman A 2019-01-27 15:27:58

If the substrings do not contain duplicates then you can use the following:

WITH testdata(string) AS (
    SELECT 'a b c' UNION ALL
    SELECT 'a b c d'
FROM testdata
    SELECT value AS substring
         , ROW_NUMBER() OVER(ORDER BY CHARINDEX(' ' + value + ' ', ' ' + string + ' ')) AS n
    FROM STRING_SPLIT(string, ' ')
) AS substrings
WHERE n = 1

The STRING_SPLIT generates the substrings but does not provide index of the substring. You can use CHARINDEX to generate the index number and it will be correct as long as the substrings are unique. It will fail for a b b c, a b c c d e, etc.

@GGadde 2018-09-17 21:07:09

I know its late, but I recently had this requirement and came up with the below code. I don't have a choice to use User defined function. Hope this helps.

                SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)
                        ),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')

@Sam K 2018-08-20 18:59:23


DECLARE @Name varchar(10) = 'John Smith'

-- Get First Name

-- Get Last Name

In my case (and in many others it seems...), I have a list of first and last names separated by a single space. This can be used directly inside a select statement to parse first and last name.

-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable

@Gorgi Rankovski 2018-04-05 10:23:24

If your database has compatibility level of 130 or higher then you can use the STRING_SPLIT function along with OFFSET FETCH clauses to get the specific item by index.

To get the item at index N (zero based), you can use the following code

SELECT value
FROM STRING_SPLIT('Hello John Smith',' ')

To check the compatibility level of your database, execute this code:

SELECT compatibility_level  
FROM sys.databases WHERE name = 'YourDBName';

@Gorgi Rankovski 2018-04-09 08:35:49

The trick is in the OFFSET 1 ROWS, which will skip the first item and will return the second item. If your indexes are 0-based and @X is the variable holding the item index you want to fetch, you can sure do OFFSET @X ROWS

@Shnugo 2018-04-09 08:40:37

Okay, did not use this before... Nice to know... I'd still prefer the xml-split based approach, as it allows to fetch the value type-safe and does not need a sub-query, but this is a good one. +1 from my side

@user1443098 2019-04-08 13:49:11

the problem here is that STRING_SPLIT does not guarantee the order of the returned results. So your item 1 may or may not be my item 1.

@Shnugo 2019-07-01 08:08:46

@GorgiRankovski, Using STRING_SPLIT demands for v2016+. In this case it is much better to use OPENJSON or JSON_VALUE. You might want to check my answer

@VinceL 2018-04-26 21:16:34

Here is a function that will accomplish the question's goal of splitting a string and accessing item X:

CREATE FUNCTION [dbo].[SplitString]
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber INT

       SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))

       DECLARE @xml XML
       SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'

       SET @ret = (SELECT
              el = split.el.value('.','varchar(max)')
       FROM  @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))

       RETURN @ret



SELECT dbo.SplitString('Hello John Smith', ' ', 2)



@Shnugo 2018-06-29 09:32:26

This is to complicated... No need for .nodes(). You can place the XQuery into .value() directly (see my answer). Btw: Scalar funcitons are very bad performers. Much better was an inline TVF, even if it returns just one cell in one row...

@zipppy 2018-03-22 14:38:24

Aaron Bertrand's answer is great, but flawed. It doesn't accurately handle a space as a delimiter (as was the example in the original question) since the length function strips trailing spaces.

The following is his code, with a small adjustment to allow for a space delimiter:

CREATE FUNCTION [dbo].[SplitString]
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim
      ) AS y

@uzr 2018-01-02 15:02:56

A modern approach using STRING_SPLIT, requires SQL Server 2016 and above.

DECLARE @string varchar(100) = 'Hello John Smith'

    ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
FROM string_split(@string, ' ')


RowNr   value
1       Hello
2       John
3       Smith

Now it is possible to get th nth element from the row number.

@Shnugo 2018-09-14 07:04:51

STRING_SPLIT does not guarantee to return the same order. But OPENJSON does (see my answer (update section))

@Victor Hugo Terceros 2017-09-04 21:52:57

Starting with SQL Server 2016 we string_split

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

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

@Johnie Karr 2017-12-28 13:47:29

This is well and good, but it doesn't address the question of getting the nth result.

@Shnugo 2018-09-14 07:05:02

STRING_SPLIT does not guarantee to return the same order. But OPENJSON does (see my answer (update section))

@Smart003 2016-07-14 05:29:41

declare @strng varchar(max)='hello john smith'
select (
        charindex(' ', @strng) + 1,
          (charindex(' ', @strng, charindex(' ', @strng) + 1))
          - charindex(' ',@strng)

@Jonesinator 2008-08-05 18:28:38

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).

You can use this simple logic:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
    IF PATINDEX('%|%', @products) > 0
        SET @individual = SUBSTRING(@products,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
        SET @individual = @products
        SET @products = NULL
        SELECT @individual

@Beth 2010-09-29 15:13:55

why SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( RTRIM( LTRIM( @p_SourceText))) and not SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( @p_SourceText)?

@Filip De Vos 2011-03-18 13:55:40

@GateKiller This solution does not support Unicode & it uses hard coded numeric(18,3) which doesn't make it a viable "reusable" function.

@jjxtra 2015-05-26 16:56:29

This works but allocates a lot of memory and wastes CPU.

@qJake 2017-04-03 20:24:41

As of SQL Server 2016, there is now a built-in function STRING_SPLIT that will split a string and return a one-column table result which you can use in a SELECT statement or elsewhere.

@Brandon Griffin 2017-05-02 19:38:24

Too bad the guys I work for aren't on 2016. But, I'll keep it in mind in case they ever get the lead out of their shoes. Great solution in the interim. I implemented it as a function and and added delimiter as an argument.

@Muhammad Ashikuzzaman 2018-11-19 16:06:15

@Jonesinator Thanks. It is the second time I need this and come here. And at last have make a SplitString Table Valued Function with this for Sql server 2008, 2010, 2014 Here The SplitString Function And it's calling mechanism

@hello_earth 2016-10-31 14:18:19

building on @NothingsImpossible solution, or, rather, comment on the most voted answer (just below the accepted one), i found the following quick-and-dirty solution fulfill my own needs - it has a benefit of being solely within SQL domain.

given a string "first;second;third;fourth;fifth", say, I want to get the third token. this works only if we know how many tokens the string is going to have - in this case it's 5. so my way of action is to chop the last two tokens away (inner query), and then to chop the first two tokens away (outer query)

i know that this is ugly and covers the specific conditions i was in, but am posting it just in case somebody finds it useful. cheers

            CHARINDEX(';', reverse_substring)
        ) reverse_substring
        select 'first;second;third;fourth;fifth' msg
    ) a
) b

@Shnugo 2018-04-09 08:42:50

this works only if we know how many tokens the string is going to have - a breaking limitation...

@Nathan Skerl 2008-10-27 16:48:51

You can leverage a Number table to do the string parsing.

Create a physical numbers table:

    create table dbo.Numbers (N int primary key);
    insert into dbo.Numbers
        select top 1000 row_number() over(order by number) from master..spt_values

Create test table with 1000000 rows

    create table #yak (i int identity(1,1) primary key, array varchar(50))

    insert into #yak(array)
        select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn

Create the function

    create function [dbo].[ufn_ParseArray]
        (   @Input      nvarchar(4000), 
            @Delimiter  char(1) = ',',
            @BaseIdent  int
    returns table as
        (   select  row_number() over (order by n asc) + (@BaseIdent - 1) [i],
                    substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
            from    dbo.Numbers
            where   n <= convert(int, len(@Input)) and
                    substring(@Delimiter + @Input, n, 1) = @Delimiter

Usage (outputs 3mil rows in 40s on my laptop)

    select * 
    from #yak 
    cross apply dbo.ufn_ParseArray(array, ',', 1)


    drop table dbo.Numbers;
    drop function  [dbo].[ufn_ParseArray]

Performance here is not amazing, but calling a function over a million row table is not the best idea. If performing a string split over many rows I would avoid the function.

@Pking 2012-12-06 13:01:09

The best solution IMO, the others have some kind of limitation.. this is fast and can parse long strings with many elements.

@hatchet - done with SOverflow 2014-10-28 16:13:14

Why do you order n descending? If there where three items, and we started numbering at 1, then the first item will be number 3, and the last will be number 1. Wouldn't it give more intuitive results if the desc were removed?

@Nathan Skerl 2014-10-28 17:43:26

Agreed, would be more intuitive in the asc direction. I was following parsename() convention which uses desc

@Tim Abell 2016-06-24 09:46:18

some explanation as to how this works would be great

@wwmbes 2016-10-28 07:41:32

In a test on 100 million rows of up to 3 fields to parse, ufn_ParseArray did not finish after 25 minutes, while REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1)) from @NothingsImpossible completed in 1.5min. @hello_earth How would your solution compare on longer strings with more than 4 fields?

@wwmbes 2016-10-28 16:23:41

On further investigation, when the @NothingsImpossible version is embedded in a function and used from there, it performs 25 times worse than when used directly in a query. Can anyone comment on why?

@Nathan Skerl 2016-10-28 17:49:10

@wwmbes Try using a physical number table with a clustered index. The usage of master..spt_values is just for illustration

@Nathan Skerl 2016-10-28 18:06:43

@wwmbes added a physical number table example

@vzczc 2008-08-05 18:57:03

First, create a function (using CTE, common table expression does away with the need for a temp table)

 create function dbo.SplitString 
        @str nvarchar(4000), 
        @separator char(1)
    returns table
    return (
        with tokens(p, a, b) AS (
                charindex(@separator, @str)
            union all
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
            p-1 zeroBasedOccurance,
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens

Then, use it as any table (or modify it to fit within your existing stored proc) like this.

select s 
from dbo.SplitString('Hello John Smith', ' ')
where zeroBasedOccurance=1


Previous version would fail for input string longer than 4000 chars. This version takes care of the limitation:

create function dbo.SplitString 
    @str nvarchar(max), 
    @separator char(1)
returns table
return (
with tokens(p, a, b) AS (
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
    p-1 ItemIndex,
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS s
from tokens


Usage remains the same.

@Pking 2012-11-07 15:31:18

It's elegant but only works for 100 elements because of the limit of recursion depth.

@Michał Powaga 2013-03-14 14:45:06

@Pking, no, the default is 100 (to prevent infinite loop). Use MAXRECURSION hint to define number of recursion levels (0 to 32767, 0 is "no limit" - may crush server). BTW, much better answer than PARSENAME, because it's universal :-). +1

@Michał Powaga 2013-03-15 09:03:46

Adding maxrecursion to this solution keep in mind this question and its answers How to setup the maxrecursion option for a CTE inside a Table-Valued-Function.

@AHiggins 2015-07-30 18:05:52

Specifically, reference the answer by Crisfole - his method slows it somewhat, but is simpler than most other options.

@Tim Abell 2016-06-24 09:45:06

minor point but the usage doesn't remain the same because you changed the column name, so s is no longer defined

@Joey Morgan 2020-02-11 21:15:34

I avoid the recursion limitation by using this code, which if I were not happily moving to SQL Server 2016 I would make a table-valued function or a scalar function, depending on my actual needs. By looping instead of self-joining you could theoretically use any size string.

@Ramazan Binarbasi 2016-01-08 14:30:11

Yet another get n'th part of string by delimeter function:

create function GetStringPartByDelimeter (
    @value as nvarchar(max),
    @delimeter as nvarchar(max),
    @position as int
) returns NVARCHAR(MAX) 
    declare @startPos as int
    declare @endPos as int
    set @endPos = -1
    while (@position > 0 and @endPos != 0) begin
        set @startPos = @endPos + 1
        set @endPos = charindex(@delimeter, @value, @startPos)

        if(@position = 1) begin
            if(@endPos = 0)
                set @endPos = len(@value) + 1

            return substring(@value, @startPos, @endPos - @startPos)

        set @position = @position - 1

    return null

and the usage:

select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)

which returns:


@James H 2016-03-29 21:16:14

I like this solution as an option to return a single substring as opposed to getting a parsed table that you then need to select from. Using a table result has its uses, but for what I needed this worked perfectly.

@Nathan Bedford 2008-08-05 18:45:19

I don't believe SQL Server has a built-in split function, so other than a UDF, the only other answer I know is to hijack the PARSENAME function:

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

PARSENAME takes a string and splits it on the period character. It takes a number as its second argument, and that number specifies which segment of the string to return (working from back to front).

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello

Obvious problem is when the string already contains a period. I still think using a UDF is the best way...any other suggestions?

@Nathan Bedford 2009-07-01 15:54:58

Thanks Saul...I should point out that this solution is really a bad solution for real development. PARSENAME only expects four parts, so using a string with more than four parts causes it to return NULL. The UDF solutions are obviously better.

@Factor Mystic 2010-07-12 14:09:43

This is a great hack, and also makes me weep that something like this is necessary for something so friggin simple in real languages.

@NothingsImpossible 2012-05-14 13:57:58

To make the indexes work in the "right" way, that is, starting at 1, i've hijacked your hijack with REVERSE: REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1)) -- Returns Hello

@Bacon Bits 2014-12-08 04:11:25

@FactorMystic First Normal Form requires that you not put multiple values in a single field. It's literally the first rule of an RDBMS. A SPLIT() function is not supplied because it encourages poor database design, and the database will never be optimized to use data stored in this format. The RDBMS is not obligated to help developers do stupid things that it has been designed not to handle. The correct answer will always be "Normalize your database like we told you 40 years ago." Neither SQL nor the RDBMS are to blame for poor design.

@MrBliz 2015-06-25 11:57:04

This is uber cool. It just so happens that the data i'm working with has four elements

@hello_earth 2016-02-08 09:09:32

+1 can always concoct a quick and dirty working solution for >4 tokens with a combination of reverse(), charindex(), substring()

@Tim Abell 2016-06-24 09:34:23

@BaconBits while I agree in theory, in practice tools like this are useful when normalizing a poor design produced by someone who came before you.

@wwmbes 2016-10-27 17:14:16

When the number of splits in the string varies from left to right, then the @NothingsImpossible route is the way to go .

@wwmbes 2016-10-28 07:23:04

@NothingsImpossible The performance of the REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1)) solution is excellent and the 4 field limitation fits my problem. Thanks!!!

@wwmbes 2016-10-28 07:29:06

@hello_earth I'd love to see what you come up with. Please show us.

@hello_earth 2016-10-31 14:20:19

@wwmbes i posted a separate answer - it's very quick and dirty and ugly, and stems from the same principles but anyway, since you asked. cheers

@Kyle Weller 2017-05-30 20:31:46

For awareness: Since parsename function is designed to return database identifiers, the value returned is limited to 128 characters (a sysname data type, which corresponds to nvarchar(128) ). If it goes beyond this, a NULL will be returned instead.

@aKiRa 2018-05-24 13:22:12

For awareness: it works only with four elements or less. If you have more than four elements the PARSENAME always return NULL, even for index lower than 5.…

@Stefan Steiger 2015-10-23 10:07:55

You can split a string in SQL without needing a function:

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'

    x.XmlCol.value('.', 'varchar(36)') AS val 
    CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

If you need to support arbitrary strings (with xml special characters)

SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'

    x.XmlCol.value('.', 'nvarchar(MAX)') AS val 
    CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 

@Ali CAKIL 2015-10-15 10:50:09

I devoloped this,

declare @x nvarchar(Max) = 'ali.veli.deli.';
declare @item nvarchar(Max);
declare @splitter char='.';

while CHARINDEX(@splitter,@x) != 0
    set @item = LEFT(@x,CHARINDEX(@splitter,@x))
    set @x    = RIGHT(@x,len(@x)-len(@item) )
     select @item as item, @x as x;

the only attention you should is dot '.' that end of the @x is always should be there.

@nazim hatipoglu 2015-02-13 09:14:22

if anyone wants to get only one part of the seperatured text can use this

select * from fromSplitStringSep('Word1 wordr2 word3',' ')

CREATE function [dbo].[SplitStringSep] 
    @str nvarchar(4000), 
    @separator char(1)
returns table
return (
    with tokens(p, a, b) AS (
        charindex(@separator, @str)
        union all
            p + 1, 
            b + 1, 
            charindex(@separator, @str, b + 1)
        from tokens
        where b > 0
            p-1 zeroBasedOccurance,
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens

@angel 2013-08-13 15:11:27

I use the answer of frederic but this did not work in SQL Server 2005

I modified it and I'm using select with union all and it works

DECLARE @str varchar(max)
SET @str = 'Hello John Smith how are you'

DECLARE @separator varchar(max)
SET @separator = ' '

DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))

SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT  ''' + @str + '''  ' 


SELECT * FROM @Splited

And the result-set is:

id  item
1   Hello
2   John
3   Smith
4   how
5   are
6   you

@Abdurrahman I. 2016-03-24 08:36:37

This is really great i've ever seen in sql stuff, it worked for my job and i appreciate that, thanks !

@Kristen Hammack 2016-08-10 13:20:44

I got really excited when I saw this because it looked so clean and easy to understand, but unfortunately you can't put this inside a UDF because of the EXEC. EXEC implicitly calls a stored procedure, and you can't use stored procedures in UDFs.

@MattE 2019-03-08 18:57:00

This Works perfectly!! i was looking into using a function(SplitStrings_Moden) from here:‌​nts that does this and it was taking a minute and a half to split the data and return the rows when only using 4 account numbers. I tested your version with a left join on the table with the data on account numbers and it took like 2 or 3 seconds! Huge difference and works flawlessly! I'd give this 20 votes if possible!

@kta 2011-11-20 06:40:26

I was looking for the solution on net and the below works for me. Ref.

And you call the function like this :

SELECT * FROM dbo.split('ram shyam hari gopal',' ')


CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))       
RETURNS @temptable TABLE (items VARCHAR(8000))       
    DECLARE @idx INT       
    DECLARE @slice VARCHAR(8000)        
    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL  RETURN       
    WHILE @idx!= 0       
        SET @idx = charindex(@Delimiter,@String)       
        IF @idx!=0       
            SET @slice = LEFT(@String,@idx - 1)       
            SET @slice = @String       
            INSERT INTO @temptable(Items) VALUES(@slice)       
        SET @String = RIGHT(@String,len(@String) - @idx)       
        IF len(@String) = 0 break       

@Björn Lindqvist 2014-10-03 09:21:53

You can't easily access the Nth item using this function.

@Frederic 2013-03-01 16:26:59

What about using string and values() statement?

DECLARE @str varchar(max)
SET @str = 'Hello John Smith'

DECLARE @separator varchar(max)
SET @separator = ' '

DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))

SET @str = REPLACE(@str, @separator, '''),(''')
SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)' 


SELECT * FROM @Splited

Result-set achieved.

id  item
1   Hello
2   John
3   Smith

@angel 2013-08-13 15:06:08

i used your answer but did not work, but i modified and this worked with union all, i am using sql 2005

@Andrey Morozov 2015-01-13 06:37:07

Pure set-based solution using TVF with recursive CTE. You can JOIN and APPLY this function to any dataset.

create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
    select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
    union all
    select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
    , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
    , [no] + 1 [no]
    from r where value > '')

select ltrim(x) [value], [no] [index] from r where x is not null;


select *
from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')
where [index] = 1;


value   index
John    1

@Savas Adar 2014-12-20 11:58:36

CREATE FUNCTION [dbo].[fnSplitString] 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)



select *from dbo.fnSplitString('Querying SQL Server','')

@Andrew Hill 2014-12-08 03:59:24

while similar to the xml based answer by josejuan, i found that processing the xml path only once, then pivoting was moderately more efficient:

select ID,
    [3] as PathProvidingID,
    [4] as PathProvider,
    [5] as ComponentProvidingID,
    [6] as ComponentProviding,
    [7] as InputRecievingID,
    [8] as InputRecieving,
    [9] as RowsPassed,
    [10] as InputRecieving2
    select id,message,d.* from sysssislog cross apply       ( 
          SELECT Item = y.i.value('(./text())[1]', 'varchar(200)'),
              row_number() over(order by y.i) as rn
             SELECT x = CONVERT(XML, '<i>' + REPLACE(Message, ':', '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY x.nodes('i') AS y(i)
       ) d
       WHERE event
    ) as tokens 
    ( max(item) for [rn] in ([3],[4],[5],[6],[7],[8],[9],[10]) 
    ) as data

ran in 8:30

select id,
tokens.value('(/n[3])', 'varchar(100)')as PathProvidingID,
tokens.value('(/n[4])', 'varchar(100)') as PathProvider,
tokens.value('(/n[5])', 'varchar(100)') as ComponentProvidingID,
tokens.value('(/n[6])', 'varchar(100)') as ComponentProviding,
tokens.value('(/n[7])', 'varchar(100)') as InputRecievingID,
tokens.value('(/n[8])', 'varchar(100)') as InputRecieving,
tokens.value('(/n[9])', 'varchar(100)') as RowsPassed
    select id, Convert(xml,'<n>'+Replace(message,'.','</n><n>')+'</n>') tokens
         from sysssislog 
       WHERE event
    ) as data

ran in 9:20

Related Questions

Sponsored Content

60 Answered Questions

[SOLVED] How do you split a list into evenly sized chunks?

78 Answered Questions

[SOLVED] How do I iterate over the words of a string?

  • 2008-10-25 08:58:21
  • Ashwin Nanjappa
  • 2177926 View
  • 2986 Score
  • 78 Answer
  • Tags:   c++ string split

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

34 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

32 Answered Questions

[SOLVED] How do I split a string on a delimiter in Bash?

22 Answered Questions

[SOLVED] Get size of all tables in database

  • 2011-10-25 16:14:34
  • Eric
  • 1346627 View
  • 1271 Score
  • 22 Answer
  • Tags:   sql-server tsql

43 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

14 Answered Questions

[SOLVED] DateTime2 vs DateTime in SQL Server

30 Answered Questions

Sponsored Content