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

<p>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"?</p>

[{'answer_id': 2657, 'author': 'Jonesinator', 'author_id': 152, 'author_profile': 'https://Stackoverflow.com/users/152', 'pm_score': 9, 'selected': True, 'text': '<p>You may find the solution in <em><a href="http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx" rel="noreferrer">SQL User Defined Function to Parse a Delimited String</a></em> helpful (from <a href="http://en.wikipedia.org/wiki/The_Code_Project" rel="noreferrer">The Code Project</a>).</p>\n\n<p>You can use this simple logic:</p>\n\n<pre><code>Declare @products varchar(200) = \'1|20|3|343|44|6|8765\'\nDeclare @individual varchar(20) = null\n\nWHILE LEN(@products) &gt; 0\nBEGIN\n    IF PATINDEX(\'%|%\', @products) &gt; 0\n    BEGIN\n        SET @individual = SUBSTRING(@products,\n                                    0,\n                                    PATINDEX(\'%|%\', @products))\n        SELECT @individual\n\n        SET @products = SUBSTRING(@products,\n                                  LEN(@individual + \'|\') + 1,\n                                  LEN(@products))\n    END\n    ELSE\n    BEGIN\n        SET @individual = @products\n        SET @products = NULL\n        SELECT @individual\n    END\nEND\n</code></pre>\n'}, {'answer_id': 2671, 'author': 'Dillie-O', 'author_id': 71, 'author_profile': 'https://Stackoverflow.com/users/71', 'pm_score': -1, 'selected': False, 'text': "<p>Well, mine isn't all that simpler, but here is the code I use to split a comma-delimited input variable into individual values, and put it into a table variable. I'm sure you could modify this slightly to split based on a space and then to do a basic SELECT query against that table variable to get your results.</p>\n\n<pre><code>-- Create temporary table to parse the list of accounting cycles.\nDECLARE @tblAccountingCycles table\n(\n    AccountingCycle varchar(10)\n)\n\nDECLARE @vchAccountingCycle varchar(10)\nDECLARE @intPosition int\n\nSET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','\nSET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)\n\nIF REPLACE(@vchAccountingCycleIDs, ',', '') &lt;&gt; ''\nBEGIN\n    WHILE @intPosition &gt; 0\n    BEGIN\n        SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))\n        IF @vchAccountingCycle &lt;&gt; ''\n        BEGIN\n            INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)\n        END\n        SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)\n        SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)\n    END\nEND\n</code></pre>\n\n<p>The concept is pretty much the same. One other alternative is to leverage the .NET compatibility within SQL Server 2005 itself. You can essentially write yourself a simple method in .NET that would split the string and then expose that as a stored procedure/function.</p>\n"}, {'answer_id': 2677, 'author': 'brendan', 'author_id': 225, 'author_profile': 'https://Stackoverflow.com/users/225', 'pm_score': 5, 'selected': False, 'text': "<p>Here is a UDF which will do it.  It will return a table of the delimited values, haven't tried all scenarios on it but your example works fine.</p>\n\n<pre><code>\nCREATE FUNCTION SplitString \n(\n    -- Add the parameters for the function here\n    @myString varchar(500),\n    @deliminator varchar(10)\n)\nRETURNS \n@ReturnTable TABLE \n(\n    -- Add the column definitions for the TABLE variable here\n    [id] [int] IDENTITY(1,1) NOT NULL,\n    [part] [varchar](50) NULL\n)\nAS\nBEGIN\n        Declare @iSpaces int\n        Declare @part varchar(50)\n\n        --initialize spaces\n        Select @iSpaces = charindex(@deliminator,@myString,0)\n        While @iSpaces > 0\n\n        Begin\n            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))\n\n            Insert Into @ReturnTable(part)\n            Select @part\n\n    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))\n\n\n            Select @iSpaces = charindex(@deliminator,@myString,0)\n        end\n\n        If len(@myString) > 0\n            Insert Into @ReturnTable\n            Select @myString\n\n    RETURN \nEND\nGO\n</code></pre>\n\n<p>You would call it like this:</p>\n\n<pre><code>\nSelect * From SplitString('Hello John Smith',' ')\n</code></pre>\n\n<p>Edit:  Updated solution to handle delimters with a len>1 as in :</p>\n\n<pre><code>\nselect * From SplitString('Hello**John**Smith','**')\n</code></pre>\n"}, {'answer_id': 2681, 'author': 'Seibar', 'author_id': 357, 'author_profile': 'https://Stackoverflow.com/users/357', 'pm_score': 3, 'selected': False, 'text': '<p>Try this:</p>\n\n<pre><code>CREATE function [SplitWordList]\n(\n @list varchar(8000)\n)\nreturns @t table \n(\n Word varchar(50) not null,\n Position int identity(1,1) not null\n)\nas begin\n  declare \n    @pos int,\n    @lpos int,\n    @item varchar(100),\n    @ignore varchar(100),\n    @dl int,\n    @a1 int,\n    @a2 int,\n    @z1 int,\n    @z2 int,\n    @n1 int,\n    @n2 int,\n    @c varchar(1),\n    @a smallint\n  select \n    @a1 = ascii(\'a\'),\n    @a2 = ascii(\'A\'),\n    @z1 = ascii(\'z\'),\n    @z2 = ascii(\'Z\'),\n    @n1 = ascii(\'0\'),\n    @n2 = ascii(\'9\')\n  set @ignore = \'\'\'"\'\n  set @pos = 1\n  set @dl = datalength(@list)\n  set @lpos = 1\n  set @item = \'\'\n  while (@pos &lt;= @dl) begin\n    set @c = substring(@list, @pos, 1)\n    if (@ignore not like \'%\' + @c + \'%\') begin\n      set @a = ascii(@c)\n      if ((@a &gt;= @a1) and (@a &lt;= @z1))  \n        or ((@a &gt;= @a2) and (@a &lt;= @z2))\n        or ((@a &gt;= @n1) and (@a &lt;= @n2))\n      begin\n        set @item = @item + @c\n      end else if (@item &gt; \'\') begin\n        insert into @t values (@item)\n        set @item = \'\'\n      end\n    end \n    set @pos = @pos + 1\n  end\n  if (@item &gt; \'\') begin\n    insert into @t values (@item)\n  end\n  return\nend\n</code></pre>\n\n<p>Test it like this:</p>\n\n<pre><code>select * from SplitWordList(\'Hello John Smith\')\n</code></pre>\n'}, {'answer_id': 2685, 'author': 'Nathan Bedford', 'author_id': 434, 'author_profile': 'https://Stackoverflow.com/users/434', 'pm_score': 9, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) \n</code></pre>\n\n<p>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).</p>\n\n<pre><code>SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello\n</code></pre>\n\n<p>Obvious problem is when the string already contains a period.  I still think using a UDF is the best way...any other suggestions?</p>\n"}, {'answer_id': 2703, 'author': 'vzczc', 'author_id': 224, 'author_profile': 'https://Stackoverflow.com/users/224', 'pm_score': 7, 'selected': False, 'text': "<p>First, create a function (using CTE, common table expression does away with the need for a temp table) </p>\n\n<pre><code> create function dbo.SplitString \n    (\n        @str nvarchar(4000), \n        @separator char(1)\n    )\n    returns table\n    AS\n    return (\n        with tokens(p, a, b) AS (\n            select \n                1, \n                1, \n                charindex(@separator, @str)\n            union all\n            select\n                p + 1, \n                b + 1, \n                charindex(@separator, @str, b + 1)\n            from tokens\n            where b &gt; 0\n        )\n        select\n            p-1 zeroBasedOccurance,\n            substring(\n                @str, \n                a, \n                case when b &gt; 0 then b-a ELSE 4000 end) \n            AS s\n        from tokens\n      )\n    GO\n</code></pre>\n\n<p>Then, use it as any table (or modify it to fit within your existing stored proc) like this. </p>\n\n<pre><code>select s \nfrom dbo.SplitString('Hello John Smith', ' ')\nwhere zeroBasedOccurance=1\n</code></pre>\n\n<p><strong>Update</strong></p>\n\n<p>Previous version would fail for input string longer than 4000 chars. This version takes care of the limitation:</p>\n\n<pre><code>create function dbo.SplitString \n(\n    @str nvarchar(max), \n    @separator char(1)\n)\nreturns table\nAS\nreturn (\nwith tokens(p, a, b) AS (\n    select \n        cast(1 as bigint), \n        cast(1 as bigint), \n        charindex(@separator, @str)\n    union all\n    select\n        p + 1, \n        b + 1, \n        charindex(@separator, @str, b + 1)\n    from tokens\n    where b &gt; 0\n)\nselect\n    p-1 ItemIndex,\n    substring(\n        @str, \n        a, \n        case when b &gt; 0 then b-a ELSE LEN(@str) end) \n    AS s\nfrom tokens\n);\n\nGO\n</code></pre>\n\n<p>Usage remains the same.</p>\n"}, {'answer_id': 240586, 'author': 'nathan_jr', 'author_id': 3769, 'author_profile': 'https://Stackoverflow.com/users/3769', 'pm_score': 5, 'selected': False, 'text': "<p>You can leverage a Number table to do the string parsing. </p>\n\n<p>Create a physical numbers table:</p>\n\n<pre><code>    create table dbo.Numbers (N int primary key);\n    insert into dbo.Numbers\n        select top 1000 row_number() over(order by number) from master..spt_values\n    go\n</code></pre>\n\n<p>Create test table with 1000000 rows</p>\n\n<pre><code>    create table #yak (i int identity(1,1) primary key, array varchar(50))\n\n    insert into #yak(array)\n        select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn\n    go\n</code></pre>\n\n<p>Create the function</p>\n\n<pre><code>    create function [dbo].[ufn_ParseArray]\n        (   @Input      nvarchar(4000), \n            @Delimiter  char(1) = ',',\n            @BaseIdent  int\n        )\n    returns table as\n    return  \n        (   select  row_number() over (order by n asc) + (@BaseIdent - 1) [i],\n                    substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s\n            from    dbo.Numbers\n            where   n &lt;= convert(int, len(@Input)) and\n                    substring(@Delimiter + @Input, n, 1) = @Delimiter\n        )\n    go\n</code></pre>\n\n<p>Usage (outputs 3mil rows in 40s on my laptop)</p>\n\n<pre><code>    select * \n    from #yak \n    cross apply dbo.ufn_ParseArray(array, ',', 1)\n</code></pre>\n\n<p>cleanup</p>\n\n<pre><code>    drop table dbo.Numbers;\n    drop function  [dbo].[ufn_ParseArray]\n</code></pre>\n\n<p>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. </p>\n"}, {'answer_id': 8199925, 'author': 'kta', 'author_id': 539023, 'author_profile': 'https://Stackoverflow.com/users/539023', 'pm_score': 3, 'selected': False, 'text': '<p>I was looking for the solution on net and the below works for me.\n<a href="http://code.developwithus.com/topics/database/mssql/" rel="nofollow">Ref</a>.</p>\n\n<p>And you call the function like this :</p>\n\n<pre><code>SELECT * FROM dbo.split(\'ram shyam hari gopal\',\' \')\n</code></pre>\n\n<hr>\n\n<pre><code>SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))       \nRETURNS @temptable TABLE (items VARCHAR(8000))       \nAS       \nBEGIN       \n    DECLARE @idx INT       \n    DECLARE @slice VARCHAR(8000)        \n    SELECT @idx = 1       \n    IF len(@String)&lt;1 OR @String IS NULL  RETURN       \n    WHILE @idx!= 0       \n    BEGIN       \n        SET @idx = charindex(@Delimiter,@String)       \n        IF @idx!=0       \n            SET @slice = LEFT(@String,@idx - 1)       \n        ELSE       \n            SET @slice = @String       \n        IF(len(@slice)&gt;0)  \n            INSERT INTO @temptable(Items) VALUES(@slice)       \n        SET @String = RIGHT(@String,len(@String) - @idx)       \n        IF len(@String) = 0 break       \n    END   \n    RETURN       \nEND\n</code></pre>\n'}, {'answer_id': 11569818, 'author': 'Damon Drake', 'author_id': 965258, 'author_profile': 'https://Stackoverflow.com/users/965258', 'pm_score': 3, 'selected': False, 'text': '<p>In my opinion you guys are making it way too complicated. Just create a CLR UDF and be done with it.</p>\n\n<pre><code>using System;\nusing System.Data;\nusing System.Data.SqlClient;\nusing System.Data.SqlTypes;\nusing Microsoft.SqlServer.Server;\nusing System.Collections.Generic;\n\npublic partial class UserDefinedFunctions {\n  [SqlFunction]\n  public static SqlString SearchString(string Search) {\n    List&lt;string&gt; SearchWords = new List&lt;string&gt;();\n    foreach (string s in Search.Split(new char[] { \' \' })) {\n      if (!s.ToLower().Equals("or") &amp;&amp; !s.ToLower().Equals("and")) {\n        SearchWords.Add(s);\n      }\n    }\n\n    return new SqlString(string.Join(" OR ", SearchWords.ToArray()));\n  }\n};\n</code></pre>\n'}, {'answer_id': 14495395, 'author': 'Prahalad Gaggar', 'author_id': 1841054, 'author_profile': 'https://Stackoverflow.com/users/1841054', 'pm_score': 1, 'selected': False, 'text': '<p>I know it\'s an old Question, but i think some one can benefit from my solution.</p>\n\n<pre><code>select \nSUBSTRING(column_name,1,CHARINDEX(\' \',column_name,1)-1)\n,SUBSTRING(SUBSTRING(column_name,CHARINDEX(\' \',column_name,1)+1,LEN(column_name))\n    ,1\n    ,CHARINDEX(\' \',SUBSTRING(column_name,CHARINDEX(\' \',column_name,1)+1,LEN(column_name)),1)-1)\n,SUBSTRING(SUBSTRING(column_name,CHARINDEX(\' \',column_name,1)+1,LEN(column_name))\n    ,CHARINDEX(\' \',SUBSTRING(column_name,CHARINDEX(\' \',column_name,1)+1,LEN(column_name)),1)+1\n    ,LEN(column_name))\nfrom table_name\n</code></pre>\n\n<p><strong><a href="http://www.sqlfiddle.com/#!3/776f3/1" rel="nofollow noreferrer">SQL FIDDLE</a></strong></p>\n\n<p><strong>Advantages:</strong></p>\n\n<ul>\n<li>It separates all the 3 sub-strings deliminator by \' \'.</li>\n<li>One must not use while loop, as it decreases the performance.</li>\n<li>No need to Pivot as all the resultant sub-string will be displayed in\none Row</li>\n</ul>\n\n<p><strong>Limitations:</strong></p>\n\n<ul>\n<li>One must know the total no. of spaces (sub-string).</li>\n</ul>\n\n<p><strong>Note</strong>: the solution can give sub-string up to to N.</p>\n\n<p>To overcame the limitation we can use the following <a href="https://stackoverflow.com/questions/14101227/how-to-use-pivot-in-a-recursive-stored-procedure/14101790#14101790"><strong>ref</strong></a>.</p>\n\n<p>But again the above <a href="https://stackoverflow.com/questions/14101227/how-to-use-pivot-in-a-recursive-stored-procedure/14101790#14101790"><strong>solution</strong></a> can\'t be use in a table (Actaully i wasn\'t able to use it).</p>\n\n<p>Again i hope this solution can help some-one.</p>\n\n<p><strong>Update:</strong> In case of Records > 50000 it is not <strong>advisable</strong> to use <strong><code>LOOPS</code></strong> as it will degrade the <strong>Performance</strong></p>\n'}, {'answer_id': 14600765, 'author': 'Sivaganesh Tamilvendhan', 'author_id': 1081699, 'author_profile': 'https://Stackoverflow.com/users/1081699', 'pm_score': 4, 'selected': False, 'text': "<p>Here I post a simple way of solution</p>\n\n<pre><code>CREATE FUNCTION [dbo].[split](\n          @delimited NVARCHAR(MAX),\n          @delimiter NVARCHAR(100)\n        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))\n        AS\n        BEGIN\n          DECLARE @xml XML\n          SET @xml = N'&lt;t&gt;' + REPLACE(@delimited,@delimiter,'&lt;/t&gt;&lt;t&gt;') + '&lt;/t&gt;'\n\n          INSERT INTO @t(val)\n          SELECT  r.value('.','varchar(MAX)') as item\n          FROM  @xml.nodes('/t') as records(r)\n          RETURN\n        END\n</code></pre>\n\n<p><br>\n    Execute the function like this<br></p>\n\n<pre><code>  select * from dbo.split('Hello John Smith',' ')\n</code></pre>\n"}, {'answer_id': 15162279, 'author': 'Frederic', 'author_id': 1839323, 'author_profile': 'https://Stackoverflow.com/users/1839323', 'pm_score': 3, 'selected': False, 'text': "<p>What about using <code>string</code> and <code>values()</code> statement?</p>\n\n<pre><code>DECLARE @str varchar(max)\nSET @str = 'Hello John Smith'\n\nDECLARE @separator varchar(max)\nSET @separator = ' '\n\nDECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))\n\nSET @str = REPLACE(@str, @separator, '''),(''')\nSET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)' \n\nINSERT INTO @Splited\nEXEC(@str)\n\nSELECT * FROM @Splited\n</code></pre>\n\n<p>Result-set achieved.</p>\n\n<pre><code>id  item\n1   Hello\n2   John\n3   Smith\n</code></pre>\n"}, {'answer_id': 15406494, 'author': 'Aleksandr Fedorenko', 'author_id': 1085940, 'author_profile': 'https://Stackoverflow.com/users/1085940', 'pm_score': 3, 'selected': False, 'text': '<p>The following example uses a recursive CTE</p>\n\n<p><strong>Update</strong> 18.09.2013</p>\n\n<pre><code>CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))\nRETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))\nAS\nBEGIN\n;WITH cte AS\n (\n  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter,  @List + @Delimiter)) AS val,\n         CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), \'\') AS nvarchar(max)) AS stval, \n         1 AS [level]\n  UNION ALL\n  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),\n         CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), \'\') AS nvarchar(max)),\n         [level] + 1\n  FROM cte\n  WHERE stval != \'\'\n  )\n  INSERT @returns\n  SELECT REPLACE(val, \' \',\'\' ) AS val, [level]\n  FROM cte\n  WHERE val &gt; \'\'\n  RETURN\nEND\n</code></pre>\n\n<p>Demo on <a href="http://sqlfiddle.com/#!3/c43e0/1" rel="nofollow"><strong>SQLFiddle</strong></a></p>\n'}, {'answer_id': 17203119, 'author': 'mkaj', 'author_id': 765326, 'author_profile': 'https://Stackoverflow.com/users/765326', 'pm_score': -1, 'selected': False, 'text': "<p>Here's my solution that may help someone. Modification of Jonesinator's answer above.</p>\n\n<p>If I have a string of delimited INT values and want a table of INTs returned (Which I can then join on).  e.g. '1,20,3,343,44,6,8765'</p>\n\n<p>Create a UDF:</p>\n\n<pre><code>IF OBJECT_ID(N'dbo.ufn_GetIntTableFromDelimitedList', N'TF') IS NOT NULL\n    DROP FUNCTION dbo.[ufn_GetIntTableFromDelimitedList];\nGO\n\nCREATE FUNCTION dbo.[ufn_GetIntTableFromDelimitedList](@String NVARCHAR(MAX),                 @Delimiter CHAR(1))\n\nRETURNS @table TABLE \n(\n    Value INT NOT NULL\n)\nAS \nBEGIN\nDECLARE @Pattern NVARCHAR(3)\nSET @Pattern = '%' + @Delimiter + '%'\nDECLARE @Value NVARCHAR(MAX)\n\nWHILE LEN(@String) &gt; 0\n    BEGIN\n        IF PATINDEX(@Pattern, @String) &gt; 0\n        BEGIN\n            SET @Value = SUBSTRING(@String, 0, PATINDEX(@Pattern, @String))\n            INSERT INTO @table (Value) VALUES (@Value)\n\n            SET @String = SUBSTRING(@String, LEN(@Value + @Delimiter) + 1, LEN(@String))\n        END\n        ELSE\n        BEGIN\n            -- Just the one value.\n            INSERT INTO @table (Value) VALUES (@String)\n            RETURN\n        END\n    END\n\nRETURN\nEND\nGO\n</code></pre>\n\n<p>Then get the table results:</p>\n\n<pre><code>SELECT * FROM dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',')\n\n1\n20\n3\n343\n44\n6\n8765\n</code></pre>\n\n<p>And in a join statement:</p>\n\n<pre><code>SELECT [ID], [FirstName]\nFROM [User] u\nJOIN dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',') t ON u.[ID] = t.[Value]\n\n1    Elvis\n20   Karen\n3    David\n343  Simon\n44   Raj\n6    Mike\n8765 Richard\n</code></pre>\n\n<p>If you want to return a list of NVARCHARs instead of INTs then just change the table definition:</p>\n\n<pre><code>RETURNS @table TABLE \n(\n    Value NVARCHAR(MAX) NOT NULL\n)\n</code></pre>\n"}, {'answer_id': 18212817, 'author': 'angel', 'author_id': 586687, 'author_profile': 'https://Stackoverflow.com/users/586687', 'pm_score': 3, 'selected': False, 'text': "<p>I use the answer of frederic but this did not work in SQL Server 2005</p>\n\n<p>I modified it and I'm using <code>select</code> with <code>union all</code> and it works</p>\n\n<pre><code>DECLARE @str varchar(max)\nSET @str = 'Hello John Smith how are you'\n\nDECLARE @separator varchar(max)\nSET @separator = ' '\n\nDECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))\n\nSET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')\nSET @str = ' SELECT  ''' + @str + '''  ' \n\nINSERT INTO @Splited\nEXEC(@str)\n\nSELECT * FROM @Splited\n</code></pre>\n\n<p>And the result-set is:</p>\n\n<pre><code>id  item\n1   Hello\n2   John\n3   Smith\n4   how\n5   are\n6   you\n</code></pre>\n"}, {'answer_id': 19779728, 'author': 'T-Rex', 'author_id': 2623456, 'author_profile': 'https://Stackoverflow.com/users/2623456', 'pm_score': 2, 'selected': False, 'text': '<pre><code>\n\n    Alter Function dbo.fn_Split\n    (\n    @Expression nvarchar(max),\n    @Delimiter  nvarchar(20) = \',\',\n    @Qualifier  char(1) = Null\n    )\n    RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))\n    AS\n    BEGIN\n       /* USAGE\n            Select * From dbo.fn_Split(\'apple pear grape banana orange honeydew cantalope 3 2 1 4\', \' \', Null)\n            Select * From dbo.fn_Split(\'1,abc,"Doe, John",4\', \',\', \'"\')\n            Select * From dbo.fn_Split(\'Hello 0,"&""&&&&\', \',\', \'"\')\n       */\n\n       -- Declare Variables\n       DECLARE\n          @X     xml,\n          @Temp  nvarchar(max),\n          @Temp2 nvarchar(max),\n          @Start int,\n          @End   int\n\n       -- HTML Encode @Expression\n       Select @Expression = (Select @Expression For XML Path(\'\'))\n\n       -- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||\n       While PATINDEX(\'%\' + @Qualifier + \'%\', @Expression) > 0 AND Len(IsNull(@Qualifier, \'\')) > 0\n       BEGIN\n          Select\n             -- Starting character position of @Qualifier\n             @Start = PATINDEX(\'%\' + @Qualifier + \'%\', @Expression),\n             -- @Expression starting at the @Start position\n             @Temp = SubString(@Expression, @Start + 1, LEN(@Expression)-@Start+1),\n             -- Next position of @Qualifier within @Expression\n             @End = PATINDEX(\'%\' + @Qualifier + \'%\', @Temp) - 1,\n             -- The part of Expression found between the @Qualifiers\n             @Temp2 = Case When @End &LT 0 Then @Temp Else Left(@Temp, @End) End,\n             -- New @Expression\n             @Expression = REPLACE(@Expression,\n                                   @Qualifier + @Temp2 + Case When @End &LT 0 Then \'\' Else @Qualifier End,\n                                   Replace(@Temp2, @Delimiter, \'|||***|||\')\n                           )\n       END\n\n       -- Replace all occurences of @Delimiter within @Expression with \'&lt/fn_Split&gt&ltfn_Split&gt\'\n       -- And convert it to XML so we can select from it\n       SET\n          @X = Cast(\'&ltfn_Split&gt\' +\n                    Replace(@Expression, @Delimiter, \'&lt/fn_Split&gt&ltfn_Split&gt\') +\n                    \'&lt/fn_Split&gt\' as xml)\n\n       -- Insert into our returnable table replacing \'|||***|||\' back to @Delimiter\n       INSERT @Results\n       SELECT\n          "Value" = LTRIM(RTrim(Replace(C.value(\'.\', \'nvarchar(max)\'), \'|||***|||\', @Delimiter)))\n       FROM\n          @X.nodes(\'fn_Split\') as X(C)\n\n       -- Return our temp table\n       RETURN\n    END\n\n</code></pre>\n'}, {'answer_id': 19935646, 'author': 'Aaron Bertrand', 'author_id': 61305, 'author_profile': 'https://Stackoverflow.com/users/61305', 'pm_score': 6, 'selected': False, 'text': '<p>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:</p>\n<pre><code>CREATE FUNCTION [dbo].[SplitString]\n    (\n        @List NVARCHAR(MAX),\n        @Delim VARCHAR(255)\n    )\n    RETURNS TABLE\n    AS\n        RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM \n          ( \n            SELECT n = Number, \n              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],\n              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))\n            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)\n              FROM sys.all_objects) AS x\n              WHERE Number &lt;= LEN(@List)\n              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim\n          ) AS y\n        );\n</code></pre>\n<p>Sample usage:</p>\n<pre><code>SELECT Value FROM dbo.SplitString(\'foo,bar,blat,foo,splunge\',\',\')\n  WHERE idx = 3;\n</code></pre>\n<p>Results:</p>\n<pre><code>----\nblat\n</code></pre>\n<p>You could also add the <code>idx</code> you want as an argument to the function, but I\'ll leave that as an exercise to the reader.</p>\n<p>You can\'t do this with <em>just</em> the <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql" rel="nofollow noreferrer">native <code>STRING_SPLIT</code> function</a> 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 <code>3,6,1</code> the result will likely be in that order, but it <em>could</em> be <code>1,3,6</code>. I have asked for the community\'s help in improving the built-in function here:</p>\n<ul>\n<li><a href="https://sqlperformance.com/2020/03/t-sql-queries/please-help-string-split-improvements" rel="nofollow noreferrer">Please help with STRING_SPLIT improvements\n</a></li>\n</ul>\n<p>With enough <em>qualitative</em> feedback, they may actually consider making some of these enhancements:</p>\n<ul>\n<li><a href="https://feedback.azure.com/d365community/idea/5eaf994d-4025-ec11-b6e6-000d3a4f0da0" rel="nofollow noreferrer">STRING_SPLIT is not feature complete</a></li>\n</ul>\n<p>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:</p>\n<ul>\n<li><a href="http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings" rel="nofollow noreferrer">Split strings the right way – or the next best way</a></li>\n<li><a href="https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up" rel="nofollow noreferrer">Splitting Strings : A Follow-Up</a></li>\n<li><a href="http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql" rel="nofollow noreferrer">Splitting Strings : Now with less T-SQL</a></li>\n<li><a href="https://sqlperformance.com/2016/01/t-sql-queries/comparing-splitting-concat" rel="nofollow noreferrer">Comparing string splitting / concatenation methods</a></li>\n<li><a href="https://sqlblog.org/2009/08/01/processing-a-list-of-integers-my-approach" rel="nofollow noreferrer">Processing a list of integers : my approach</a></li>\n<li><a href="https://sqlblog.org/2010/07/07/splitting-a-list-of-integers-another-roundup" rel="nofollow noreferrer">Splitting a list of integers : another roundup</a></li>\n<li><a href="https://sqlblog.org/2009/08/06/more-on-splitting-lists-custom-delimiters-preventing-duplicates-and-maintaining-order" rel="nofollow noreferrer">More on splitting lists : custom delimiters, preventing duplicates, and maintaining order</a></li>\n<li><a href="https://www.mssqltips.com/sqlservertip/4140/removing-duplicates-from-strings-in-sql-server/?utm_source=AaronBertrand" rel="nofollow noreferrer">Removing Duplicates from Strings in SQL Server</a></li>\n</ul>\n<p>On SQL Server 2016 or above, though, you should look at <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql" rel="nofollow noreferrer"><code>STRING_SPLIT()</code></a> and <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql" rel="nofollow noreferrer"><code>STRING_AGG()</code></a>:</p>\n<ul>\n<li><a href="https://sqlperformance.com/2016/03/sql-server-2016/string-split" rel="nofollow noreferrer">Performance Surprises and Assumptions : STRING_SPLIT()</a></li>\n<li><a href="https://sqlperformance.com/2016/04/sql-server-2016/string-split-follow-up-1" rel="nofollow noreferrer">STRING_SPLIT() in SQL Server 2016 : Follow-Up #1</a></li>\n<li><a href="https://sqlperformance.com/2016/04/sql-server-2016/string-split-follow-up-2" rel="nofollow noreferrer">STRING_SPLIT() in SQL Server 2016 : Follow-Up #2</a></li>\n<li><a href="https://sqlperformance.com/2016/12/sql-performance/sql-server-v-next-string_agg-performance" rel="nofollow noreferrer">SQL Server v.Next : STRING_AGG() performance</a></li>\n<li><a href="https://www.mssqltips.com/sqlservertip/5275/solve-old-problems-with-sql-servers-new-stringagg-and-stringsplit-functions/?utm_source=AaronBertrand" rel="nofollow noreferrer">Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions</a></li>\n</ul>\n'}, {'answer_id': 21159352, 'author': 'dani herrera', 'author_id': 842935, 'author_profile': 'https://Stackoverflow.com/users/842935', 'pm_score': 0, 'selected': False, 'text': '<p>Recursive CTE solution with server pain, <a href="http://sqlfiddle.com/#!3/9ebaa/1" rel="nofollow">test it</a></p>\n\n<p><strong>MS SQL Server 2008 Schema Setup</strong>:</p>\n\n<pre><code>create table Course( Courses varchar(100) );\ninsert into Course values (\'Hello John Smith\');\n</code></pre>\n\n<p><strong>Query 1</strong>:</p>\n\n<pre><code>with cte as\n   ( select \n        left( Courses, charindex( \' \' , Courses) ) as a_l,\n        cast( substring( Courses, \n                         charindex( \' \' , Courses) + 1 , \n                         len(Courses ) ) + \' \' \n              as varchar(100) )  as a_r,\n        Courses as a,\n        0 as n\n     from Course t\n    union all\n      select \n        left(a_r, charindex( \' \' , a_r) ) as a_l,\n        substring( a_r, charindex( \' \' , a_r) + 1 , len(a_R ) ) as a_r,\n        cte.a,\n        cte.n + 1 as n\n    from Course t inner join cte \n         on t.Courses = cte.a and len( a_r ) &gt; 0\n\n   )\nselect a_l, n from cte\n--where N = 1\n</code></pre>\n\n<p><strong><a href="http://sqlfiddle.com/#!3/9ebaa/1/0" rel="nofollow">Results</a></strong>:</p>\n\n<pre><code>|    A_L | N |\n|--------|---|\n| Hello  | 0 |\n|  John  | 1 |\n| Smith  | 2 |\n</code></pre>\n'}, {'answer_id': 22536373, 'author': 'Matt Watson', 'author_id': 884742, 'author_profile': 'https://Stackoverflow.com/users/884742', 'pm_score': -1, 'selected': False, 'text': '<p>Here is a SQL UDF that can split a string and grab just a certain piece.</p>\n\n<pre><code>create FUNCTION [dbo].[udf_SplitParseOut]\n(\n    @List nvarchar(MAX),\n    @SplitOn nvarchar(5),\n    @GetIndex smallint\n)  \nreturns varchar(1000)\nAS  \n\nBEGIN\n\nDECLARE @RtnValue table \n(\n\n    Id int identity(0,1),\n    Value nvarchar(MAX)\n) \n\n\n    DECLARE @result varchar(1000)\n\n    While (Charindex(@SplitOn,@List)&gt;0)\n    Begin\n        Insert Into @RtnValue (value)\n        Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))\n        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))\n    End\n\n    Insert Into @RtnValue (Value)\n    Select Value = ltrim(rtrim(@List))\n\n    select @result = value from @RtnValue where ID = @GetIndex\n\n    Return @result\nEND\n</code></pre>\n'}, {'answer_id': 23403089, 'author': 'Mohsen', 'author_id': 514329, 'author_profile': 'https://Stackoverflow.com/users/514329', 'pm_score': -1, 'selected': False, 'text': "<p>A simple optimized algorithm :</p>\n\n<pre><code>ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )\nRETURNS @Result TABLE ( value NVARCHAR(50)) \nAS\nBEGIN\n    DECLARE @PathInd INT\n    Set @Text+=@Splitor\n    WHILE LEN(@Text) &gt; 0\n    BEGIN\n        SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)\n        INSERT INTO  @Result VALUES(SUBSTRING(@Text, 0, @PathInd))\n        SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))\n    END\n        RETURN \nEND\n</code></pre>\n"}, {'answer_id': 25391776, 'author': 'Katherine Elizabeth Lightsey', 'author_id': 1919621, 'author_profile': 'https://Stackoverflow.com/users/1919621', 'pm_score': -1, 'selected': False, 'text': '<p>I\'ve been using vzczc\'s answer using recursive cte\'s for some time, but have wanted to update it to handle a variable length separator and also to handle strings with leading and lagging "separators" such as when you have a csv file with records such as:</p>\n\n<p><strong>"Bob","Smith","Sunnyvale","CA"</strong></p>\n\n<p>or when you are dealing with six part fqn\'s as shown below. I use these extensively for logging of the subject_fqn for auditing, error handling, etc. and parsename only handles four parts:</p>\n\n<pre><code>[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]\n</code></pre>\n\n<p>Here is my updated version, and thanks to vzczc\'s for his original post!</p>\n\n<pre><code>select * from [utility].[split_string](N\'"this"."string"."gets"."split"."and"."removes"."leading"."and"."trailing"."quotes"\', N\'"."\', N\'"\', N\'"\');\n\nselect * from [utility].[split_string](N\'"this"."string"."gets"."split"."but"."leaves"."leading"."and"."trailing"."quotes"\', N\'"."\', null, null);\n\nselect * from [utility].[split_string](N\'[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]\', N\'].[\', N\'[\', N\']\');\n\ncreate function [utility].[split_string] ( \n  @input       [nvarchar](max) \n  , @separator [sysname] \n  , @lead      [sysname] \n  , @lag       [sysname]) \nreturns @node_list table ( \n  [index]  [int] \n  , [node] [nvarchar](max)) \n  begin \n      declare @separator_length [int]= len(@separator) \n              , @lead_length    [int] = isnull(len(@lead), 0) \n              , @lag_length     [int] = isnull(len(@lag), 0); \n      -- \n      set @input = right(@input, len(@input) - @lead_length); \n      set @input = left(@input, len(@input) - @lag_length); \n      -- \n      with [splitter]([index], [starting_position], [start_location]) \n           as (select cast(@separator_length as [bigint]) \n                      , cast(1 as [bigint]) \n                      , charindex(@separator, @input) \n               union all \n               select [index] + 1 \n                      , [start_location] + @separator_length \n                      , charindex(@separator, @input, [start_location] + @separator_length) \n               from   [splitter] \n               where  [start_location] &gt; 0) \n      -- \n      insert into @node_list \n                  ([index],[node]) \n        select [index] - @separator_length                   as [index] \n               , substring(@input, [starting_position], case \n                                                            when [start_location] &gt; 0 \n                                                                then \n                                                              [start_location] - [starting_position] \n                                                            else \n                                                              len(@input) \n                                                        end) as [node] \n        from   [splitter]; \n      -- \n      return; \n  end; \ngo \n</code></pre>\n'}, {'answer_id': 25511109, 'author': 'jjxtra', 'author_id': 56079, 'author_profile': 'https://Stackoverflow.com/users/56079', 'pm_score': 1, 'selected': False, 'text': '<p>Almost all the other answers are replacing the string being split which wastes CPU cycles and performs unnecessary memory allocations.</p>\n\n<p>I cover a much better way to do a string split here: <a href="http://www.digitalruby.com/split-string-sql-server/" rel="nofollow noreferrer">http://www.digitalruby.com/split-string-sql-server/</a></p>\n\n<p>Here is the code:</p>\n\n<pre><code>SET NOCOUNT ON\n\n-- 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\nDECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)\nDECLARE @StringToSplit nvarchar(MAX) = \'your|string|to|split|here\'\nDECLARE @SplitEndPos int\nDECLARE @SplitValue nvarchar(MAX)\nDECLARE @SplitDelim nvarchar(1) = \'|\'\nDECLARE @SplitStartPos int = 1\n\nSET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)\n\nWHILE @SplitEndPos &gt; 0\nBEGIN\n    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))\n    INSERT @SplitStringTable (Value) VALUES (@SplitValue)\n    SET @SplitStartPos = @SplitEndPos + 1\n    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)\nEND\n\nSET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)\nINSERT @SplitStringTable (Value) VALUES(@SplitValue)\n\nSET NOCOUNT OFF\n\n-- You can select or join with the values in @SplitStringTable at this point.\n</code></pre>\n'}, {'answer_id': 26867232, 'author': 'josejuan', 'author_id': 1540749, 'author_profile': 'https://Stackoverflow.com/users/1540749', 'pm_score': 3, 'selected': False, 'text': "<p>This pattern works fine and you can generalize</p>\n\n<pre><code>Convert(xml,'&lt;n&gt;'+Replace(FIELD,'.','&lt;/n&gt;&lt;n&gt;')+'&lt;/n&gt;').value('(/n[INDEX])','TYPE')\n                          ^^^^^                                   ^^^^^     ^^^^\n</code></pre>\n\n<p>note <strong>FIELD</strong>, <strong>INDEX</strong> and <strong>TYPE</strong>.</p>\n\n<p>Let some table with identifiers like</p>\n\n<pre><code>sys.message.1234.warning.A45\nsys.message.1235.error.O98\n....\n</code></pre>\n\n<p>Then, you can write</p>\n\n<pre><code>SELECT Source         = q.value('(/n[1])', 'varchar(10)'),\n       RecordType     = q.value('(/n[2])', 'varchar(20)'),\n       RecordNumber   = q.value('(/n[3])', 'int'),\n       Status         = q.value('(/n[4])', 'varchar(5)')\nFROM   (\n         SELECT   q = Convert(xml,'&lt;n&gt;'+Replace(fieldName,'.','&lt;/n&gt;&lt;n&gt;')+'&lt;/n&gt;')\n         FROM     some_TABLE\n       ) Q\n</code></pre>\n\n<p>splitting and casting all parts.</p>\n"}, {'answer_id': 27351204, 'author': 'Andrew Hill', 'author_id': 432976, 'author_profile': 'https://Stackoverflow.com/users/432976', 'pm_score': 0, 'selected': False, 'text': "<p>while similar to the xml based answer by josejuan, i found that processing the xml path only once, then pivoting was moderately more efficient:</p>\n\n<pre><code>select ID,\n    [3] as PathProvidingID,\n    [4] as PathProvider,\n    [5] as ComponentProvidingID,\n    [6] as ComponentProviding,\n    [7] as InputRecievingID,\n    [8] as InputRecieving,\n    [9] as RowsPassed,\n    [10] as InputRecieving2\n    from\n    (\n    select id,message,d.* from sysssislog cross apply       ( \n          SELECT Item = y.i.value('(./text())[1]', 'varchar(200)'),\n              row_number() over(order by y.i) as rn\n          FROM \n          ( \n             SELECT x = CONVERT(XML, '&lt;i&gt;' + REPLACE(Message, ':', '&lt;/i&gt;&lt;i&gt;') + '&lt;/i&gt;').query('.')\n          ) AS a CROSS APPLY x.nodes('i') AS y(i)\n       ) d\n       WHERE event\n       = \n       'OnPipelineRowsSent'\n    ) as tokens \n    pivot \n    ( max(item) for [rn] in ([3],[4],[5],[6],[7],[8],[9],[10]) \n    ) as data\n</code></pre>\n\n<p>ran in 8:30</p>\n\n<pre><code>select id,\ntokens.value('(/n[3])', 'varchar(100)')as PathProvidingID,\ntokens.value('(/n[4])', 'varchar(100)') as PathProvider,\ntokens.value('(/n[5])', 'varchar(100)') as ComponentProvidingID,\ntokens.value('(/n[6])', 'varchar(100)') as ComponentProviding,\ntokens.value('(/n[7])', 'varchar(100)') as InputRecievingID,\ntokens.value('(/n[8])', 'varchar(100)') as InputRecieving,\ntokens.value('(/n[9])', 'varchar(100)') as RowsPassed\n from\n(\n    select id, Convert(xml,'&lt;n&gt;'+Replace(message,'.','&lt;/n&gt;&lt;n&gt;')+'&lt;/n&gt;') tokens\n         from sysssislog \n       WHERE event\n       = \n       'OnPipelineRowsSent'\n    ) as data\n</code></pre>\n\n<p>ran in 9:20</p>\n"}, {'answer_id': 27580011, 'author': 'Savas Adar', 'author_id': 793880, 'author_profile': 'https://Stackoverflow.com/users/793880', 'pm_score': 0, 'selected': False, 'text': "<pre><code>CREATE FUNCTION [dbo].[fnSplitString] \n( \n    @string NVARCHAR(MAX), \n    @delimiter CHAR(1) \n) \nRETURNS @output TABLE(splitdata NVARCHAR(MAX) \n) \nBEGIN \n    DECLARE @start INT, @end INT \n    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) \n    WHILE @start &lt; LEN(@string) + 1 BEGIN \n        IF @end = 0  \n            SET @end = LEN(@string) + 1\n\n        INSERT INTO @output (splitdata)  \n        VALUES(SUBSTRING(@string, @start, @end - @start)) \n        SET @start = @end + 1 \n        SET @end = CHARINDEX(@delimiter, @string, @start)\n\n    END \n    RETURN \nEND\n</code></pre>\n\n<p>AND USE IT</p>\n\n<pre><code>select *from dbo.fnSplitString('Querying SQL Server','')\n</code></pre>\n"}, {'answer_id': 27916227, 'author': 'Andrey Morozov', 'author_id': 483408, 'author_profile': 'https://Stackoverflow.com/users/483408', 'pm_score': 1, 'selected': False, 'text': "<p>Pure set-based solution using <code>TVF</code> with recursive <code>CTE</code>. You can <code>JOIN</code> and <code>APPLY</code> this function to any dataset.</p>\n\n<pre><code>create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))\nreturns table\nas return\nwith r as (\n    select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j\n    union all\n    select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]\n    , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]\n    , [no] + 1 [no]\n    from r where value &gt; '')\n\nselect ltrim(x) [value], [no] [index] from r where x is not null;\ngo\n</code></pre>\n\n<p>Usage:</p>\n\n<pre><code>select *\nfrom [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')\nwhere [index] = 1;\n</code></pre>\n\n<p>Result:</p>\n\n<pre><code>value   index\n-------------\nJohn    1\n</code></pre>\n"}, {'answer_id': 28496048, 'author': 'nazim hatipoglu', 'author_id': 807905, 'author_profile': 'https://Stackoverflow.com/users/807905', 'pm_score': 0, 'selected': False, 'text': "<p><strong>if anyone wants to get only one part of the seperatured text can use this</strong></p>\n\n<p>select * from fromSplitStringSep('Word1 wordr2 word3',' ') </p>\n\n<pre><code>CREATE function [dbo].[SplitStringSep] \n(\n    @str nvarchar(4000), \n    @separator char(1)\n)\nreturns table\nAS\nreturn (\n    with tokens(p, a, b) AS (\n        select \n        1, \n        1, \n        charindex(@separator, @str)\n        union all\n        select\n            p + 1, \n            b + 1, \n            charindex(@separator, @str, b + 1)\n        from tokens\n        where b &gt; 0\n        )\n        select\n            p-1 zeroBasedOccurance,\n            substring(\n                @str, \n                a, \n                case when b &gt; 0 then b-a ELSE 4000 end) \n            AS s\n        from tokens\n  )\n</code></pre>\n"}, {'answer_id': 33146513, 'author': 'Ali CAKIL', 'author_id': 3303448, 'author_profile': 'https://Stackoverflow.com/users/3303448', 'pm_score': 0, 'selected': False, 'text': "<p>I devoloped this,</p>\n\n<pre><code>declare @x nvarchar(Max) = 'ali.veli.deli.';\ndeclare @item nvarchar(Max);\ndeclare @splitter char='.';\n\nwhile CHARINDEX(@splitter,@x) != 0\nbegin\n    set @item = LEFT(@x,CHARINDEX(@splitter,@x))\n    set @x    = RIGHT(@x,len(@x)-len(@item) )\n     select @item as item, @x as x;\nend\n</code></pre>\n\n<p>the only attention you should is dot '.' that end of the @x is always should be there.</p>\n"}, {'answer_id': 33299809, 'author': 'Stefan Steiger', 'author_id': 155077, 'author_profile': 'https://Stackoverflow.com/users/155077', 'pm_score': 2, 'selected': False, 'text': '<p>You can split a string in SQL without needing a function:</p>\n\n<pre><code>DECLARE @bla varchar(MAX)\nSET @bla = \'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C\'\n\n-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes\nSELECT \n    x.XmlCol.value(\'.\', \'varchar(36)\') AS val \nFROM \n(\n    SELECT \n    CAST(\'&lt;e&gt;\' + REPLACE(@bla, \',\', \'&lt;/e&gt;&lt;e&gt;\') + \'&lt;/e&gt;\' AS xml) AS RawXml\n) AS b \nCROSS APPLY b.RawXml.nodes(\'e\') x(XmlCol);\n</code></pre>\n\n<p>If you need to support arbitrary strings (with xml special characters)</p>\n\n<pre><code>DECLARE @bla NVARCHAR(MAX)\nSET @bla = \'&lt;html&gt;unsafe &amp; safe Utf8CharsDon\'\'tGetEncoded ÄöÜ - "Conex"&lt;html&gt;,Barnes &amp; Noble,abc,def,ghi\'\n\n-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes\nSELECT \n    x.XmlCol.value(\'.\', \'nvarchar(MAX)\') AS val \nFROM \n(\n    SELECT \n    CAST(\'&lt;e&gt;\' + REPLACE((SELECT @bla FOR XML PATH(\'\')), \',\', \'&lt;/e&gt;&lt;e&gt;\') + \'&lt;/e&gt;\' AS xml) AS RawXml\n) AS b \nCROSS APPLY b.RawXml.nodes(\'e\') x(XmlCol); \n</code></pre>\n'}, {'answer_id': 34679417, 'author': 'Ramazan Binarbasi', 'author_id': 2266524, 'author_profile': 'https://Stackoverflow.com/users/2266524', 'pm_score': 3, 'selected': False, 'text': "<p>Yet another get n'th part of string by delimeter function:</p>\n\n<pre><code>create function GetStringPartByDelimeter (\n    @value as nvarchar(max),\n    @delimeter as nvarchar(max),\n    @position as int\n) returns NVARCHAR(MAX) \nAS BEGIN\n    declare @startPos as int\n    declare @endPos as int\n    set @endPos = -1\n    while (@position &gt; 0 and @endPos != 0) begin\n        set @startPos = @endPos + 1\n        set @endPos = charindex(@delimeter, @value, @startPos)\n\n        if(@position = 1) begin\n            if(@endPos = 0)\n                set @endPos = len(@value) + 1\n\n            return substring(@value, @startPos, @endPos - @startPos)\n        end\n\n        set @position = @position - 1\n    end\n\n    return null\nend\n</code></pre>\n\n<p>and the usage:</p>\n\n<pre><code>select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)\n</code></pre>\n\n<p>which returns:</p>\n\n<pre><code>c\n</code></pre>\n"}, {'answer_id': 38275075, 'author': 'Shnugo', 'author_id': 5089204, 'author_profile': 'https://Stackoverflow.com/users/5089204', 'pm_score': 5, 'selected': False, 'text': '<p>This question is <strong>not about a string split approach</strong>, but about <strong>how to get the nth element</strong>.</p>\n<p>All answers here are doing some kind of string splitting using recursion, <code>CTE</code>s, multiple <code>CHARINDEX</code>, <code>REVERSE</code> and <code>PATINDEX</code>, inventing functions, call for CLR methods, number tables, <code>CROSS APPLY</code>s ... Most answers cover many lines of code.</p>\n<p>But - if you really <strong>want nothing more than an approach to get the nth element</strong> - this can be done as <strong>real one-liner</strong>, no UDF, not even a sub-select... And as an extra benefit: <strong>type safe</strong></p>\n<p>Get part 2 delimited by a space:</p>\n<pre><code>DECLARE @input NVARCHAR(100)=N\'part1 part2 part3\';\nSELECT CAST(N\'&lt;x&gt;\' + REPLACE(@input,N\' \',N\'&lt;/x&gt;&lt;x&gt;\') + N\'&lt;/x&gt;\' AS XML).value(\'/x[2]\',\'nvarchar(max)\')\n</code></pre>\n<p>Of course <strong>you can use variables</strong> for delimiter and position (use <code>sql:column</code> to retrieve the position directly from a query\'s value):</p>\n<pre><code>DECLARE @dlmt NVARCHAR(10)=N\' \';\nDECLARE @pos INT = 2;\nSELECT CAST(N\'&lt;x&gt;\' + REPLACE(@input,@dlmt,N\'&lt;/x&gt;&lt;x&gt;\') + N\'&lt;/x&gt;\' AS XML).value(\'/x[sql:variable(&quot;@pos&quot;)][1]\',\'nvarchar(max)\')\n</code></pre>\n<p>If your string might include <strong>forbidden characters</strong> (especially one among <code>&amp;&gt;&lt;</code>), you still can do it this way. Just use <code>FOR XML PATH</code> on your string first to replace all forbidden characters with the fitting escape sequence implicitly.</p>\n<p>It\'s a very special case if - additionally - <strong>your delimiter is the semicolon</strong>. In this case I replace the delimiter first to \'#DLMT#\', and replace this to the XML tags finally:</p>\n<pre><code>SET @input=N\'Some &lt;, &gt; and &amp;;Other äöü@€;One more\';\nSET @dlmt=N\';\';\nSELECT CAST(N\'&lt;x&gt;\' + REPLACE((SELECT REPLACE(@input,@dlmt,\'#DLMT#\') AS [*] FOR XML PATH(\'\')),N\'#DLMT#\',N\'&lt;/x&gt;&lt;x&gt;\') + N\'&lt;/x&gt;\' AS XML).value(\'/x[sql:variable(&quot;@pos&quot;)][1]\',\'nvarchar(max)\');\n</code></pre>\n<h2>UPDATE for SQL-Server 2016+</h2>\n<p>Regretfully the developers forgot to return the part\'s index with <code>STRING_SPLIT</code>. But, using SQL-Server 2016+, there is <code>JSON_VALUE</code> and <code>OPENJSON</code>.</p>\n<p>With <code>JSON_VALUE</code> we can pass in the position as the index\' array.</p>\n<p>For <code>OPENJSON</code> the <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017" rel="noreferrer">documentation</a> states clearly:</p>\n<blockquote>\n<p>When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.</p>\n</blockquote>\n<p>A string like <code>1,2,3</code> needs nothing more than brackets: <code>[1,2,3]</code>.<br />\nA string of words like <code>this is an example</code> needs to be <code>[&quot;this&quot;,&quot;is&quot;,&quot;an&quot;,&quot;example&quot;]</code>.<br />\nThese are very easy string operations. Just try it out:</p>\n<pre><code>DECLARE @str VARCHAR(100)=\'Hello John Smith\';\nDECLARE @position INT = 2;\n\n--We can build the json-path \'$[1]\' using CONCAT\nSELECT JSON_VALUE(\'[&quot;\' + REPLACE(@str,\' \',\'&quot;,&quot;\') + \'&quot;]\',CONCAT(\'$[\',@position-1,\']\'));\n</code></pre>\n<p>--See this for a position safe string-splitter (<em>zero-based</em>):</p>\n<pre><code>SELECT  JsonArray.[key] AS [Position]\n       ,JsonArray.[value] AS [Part]\nFROM OPENJSON(\'[&quot;\' + REPLACE(@str,\' \',\'&quot;,&quot;\') + \'&quot;]\') JsonArray\n</code></pre>\n<p>In <a href="https://stackoverflow.com/a/56617711/5089204">this post</a> I tested various approaches and found, that <code>OPENJSON</code> is really fast. Even much faster than the famous &quot;delimitedSplit8k()&quot; method...</p>\n<h2>UPDATE 2 - Get the values type-safe</h2>\n<p>We can use an <em>array within an array</em> simply by using doubled <code>[[]]</code>. This allows for a typed <code>WITH</code>-clause:</p>\n<pre><code>DECLARE  @SomeDelimitedString VARCHAR(100)=\'part1|1|20190920\';\n\nDECLARE @JsonArray NVARCHAR(MAX)=CONCAT(\'[[&quot;\',REPLACE(@SomeDelimitedString,\'|\',\'&quot;,&quot;\'),\'&quot;]]\');\n\nSELECT @SomeDelimitedString          AS TheOriginal\n      ,@JsonArray                    AS TransformedToJSON\n      ,ValuesFromTheArray.*\nFROM OPENJSON(@JsonArray)\nWITH(TheFirstFragment  VARCHAR(100) \'$[0]\'\n    ,TheSecondFragment INT          \'$[1]\'\n    ,TheThirdFragment  DATE         \'$[2]\') ValuesFromTheArray\n</code></pre>\n'}, {'answer_id': 38366116, 'author': 'Smart003', 'author_id': 3835573, 'author_profile': 'https://Stackoverflow.com/users/3835573', 'pm_score': 0, 'selected': False, 'text': "<pre><code>declare @strng varchar(max)='hello john smith'\nselect (\n    substring(\n        @strng,\n        charindex(' ', @strng) + 1,\n        (\n          (charindex(' ', @strng, charindex(' ', @strng) + 1))\n          - charindex(' ',@strng)\n        )\n    ))\n</code></pre>\n"}, {'answer_id': 40344039, 'author': 'hello_earth', 'author_id': 57033, 'author_profile': 'https://Stackoverflow.com/users/57033', 'pm_score': 0, 'selected': False, 'text': '<p>building on @NothingsImpossible solution, or, rather, comment on the most voted answer (just below the accepted one), i found the following <strong>quick-and-dirty</strong> solution fulfill my own needs - it has a benefit of being solely within SQL domain.</p>\n\n<p>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)</p>\n\n<p>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</p>\n\n<pre><code>select \n    REVERSE(\n        SUBSTRING(\n            reverse_substring, \n            0, \n            CHARINDEX(\';\', reverse_substring)\n        )\n    ) \nfrom \n(\n    select \n        msg,\n        SUBSTRING(\n            REVERSE(msg), \n            CHARINDEX(\n                \';\', \n                REVERSE(msg), \n                CHARINDEX(\n                    \';\',\n                    REVERSE(msg)\n                )+1\n            )+1,\n            1000\n        ) reverse_substring\n    from \n    (\n        select \'first;second;third;fourth;fifth\' msg\n    ) a\n) b\n</code></pre>\n'}, {'answer_id': 46044428, 'author': 'Victor Hugo Terceros', 'author_id': 8067407, 'author_profile': 'https://Stackoverflow.com/users/8067407', 'pm_score': 0, 'selected': False, 'text': "<p>Starting with <strong>SQL Server 2016</strong> we <strong>string_split</strong></p>\n\n<pre><code>DECLARE @string varchar(100) = 'Richard, Mike, Mark'\n\nSELECT value FROM string_split(@string, ',')\n</code></pre>\n"}, {'answer_id': 48063226, 'author': 'uzr', 'author_id': 1820424, 'author_profile': 'https://Stackoverflow.com/users/1820424', 'pm_score': 0, 'selected': False, 'text': '<p>A modern approach using <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql" rel="nofollow noreferrer">STRING_SPLIT</a>, requires SQL Server 2016 and above.</p>\n\n<pre><code>DECLARE @string varchar(100) = \'Hello John Smith\'\n\nSELECT\n    ROW_NUMBER() OVER (ORDER BY value) AS RowNr,\n    value\nFROM string_split(@string, \' \')\n</code></pre>\n\n<p>Result:</p>\n\n<pre><code>RowNr   value\n1       Hello\n2       John\n3       Smith\n</code></pre>\n\n<p>Now it is possible to get th nth element from the row number.</p>\n'}, {'answer_id': 49431435, 'author': 'zipppy', 'author_id': 2639265, 'author_profile': 'https://Stackoverflow.com/users/2639265', 'pm_score': 0, 'selected': False, 'text': "<p>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. </p>\n\n<p>The following is his code, with a small adjustment to allow for a space delimiter:</p>\n\n<pre><code>CREATE FUNCTION [dbo].[SplitString]\n(\n    @List NVARCHAR(MAX),\n    @Delim VARCHAR(255)\n)\nRETURNS TABLE\nAS\n    RETURN ( SELECT [Value] FROM \n      ( \n        SELECT \n          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],\n          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))\n        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)\n          FROM sys.all_objects) AS x\n          WHERE Number &lt;= LEN(@List)\n          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim\n      ) AS y\n    );\n</code></pre>\n"}, {'answer_id': 49669994, 'author': 'Gorgi Rankovski', 'author_id': 632604, 'author_profile': 'https://Stackoverflow.com/users/632604', 'pm_score': 3, 'selected': False, 'text': '<p>If your database has compatibility level of 130 or higher then you can use the <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql" rel="nofollow noreferrer">STRING_SPLIT</a> function along with <a href="https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx" rel="nofollow noreferrer">OFFSET FETCH</a> clauses to get the specific item by index.</p>\n\n<p>To get the item at <strong>index N</strong> (zero based), you can use the following code</p>\n\n<pre><code>SELECT value\nFROM STRING_SPLIT(\'Hello John Smith\',\' \')\nORDER BY (SELECT NULL)\nOFFSET N ROWS\nFETCH NEXT 1 ROWS ONLY\n</code></pre>\n\n<p>To check the <a href="https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database" rel="nofollow noreferrer">compatibility level of your database</a>, execute this code:</p>\n\n<pre><code>SELECT compatibility_level  \nFROM sys.databases WHERE name = \'YourDBName\';\n</code></pre>\n'}, {'answer_id': 50051953, 'author': 'VinceL', 'author_id': 9550018, 'author_profile': 'https://Stackoverflow.com/users/9550018', 'pm_score': 0, 'selected': False, 'text': '<p>Here is a function that will accomplish the question\'s goal of splitting a string and accessing item X:</p>\n\n<pre><code>CREATE FUNCTION [dbo].[SplitString]\n(\n   @List       VARCHAR(MAX),\n   @Delimiter  VARCHAR(255),\n   @ElementNumber INT\n)\nRETURNS VARCHAR(MAX)\nAS\nBEGIN\n\n       DECLARE @inp VARCHAR(MAX)\n       SET @inp = (SELECT REPLACE(@List,@Delimiter,\'_DELMTR_\') FOR XML PATH(\'\'))\n\n       DECLARE @xml XML\n       SET @xml = \'&lt;split&gt;&lt;el&gt;\' + REPLACE(@inp,\'_DELMTR_\',\'&lt;/el&gt;&lt;el&gt;\') + \'&lt;/el&gt;&lt;/split&gt;\'\n\n       DECLARE @ret VARCHAR(MAX)\n       SET @ret = (SELECT\n              el = split.el.value(\'.\',\'varchar(max)\')\n       FROM  @xml.nodes(\'/split/el[string-length(.)&gt;0][position() = sql:variable("@elementnumber")]\') split(el))\n\n       RETURN @ret\n\nEND\n</code></pre>\n\n<p>Usage:</p>\n\n<pre><code>SELECT dbo.SplitString(\'Hello John Smith\', \' \', 2)\n</code></pre>\n\n<p>Result:</p>\n\n<pre><code>John\n</code></pre>\n'}, {'answer_id': 51936971, 'author': 'Sam K', 'author_id': 5100074, 'author_profile': 'https://Stackoverflow.com/users/5100074', 'pm_score': 0, 'selected': False, 'text': "<p><strong>SIMPLE SOLUTION FOR PARSING FIRST AND LAST NAME</strong></p>\n\n<pre><code>DECLARE @Name varchar(10) = 'John Smith'\n\n-- Get First Name\nSELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))\n\n-- Get Last Name\nSELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))\n</code></pre>\n\n<p>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.</p>\n\n<pre><code>-- i.e. Get First and Last Name from a table of Full Names\nSELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,\nSUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,\nFrom FullNameTable\n</code></pre>\n"}, {'answer_id': 52375705, 'author': 'GGadde', 'author_id': 1550510, 'author_profile': 'https://Stackoverflow.com/users/1550510', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n\n<pre><code>SELECT \n    SUBSTRING(\n                SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)\n                        ),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')\n            )\n</code></pre>\n"}, {'answer_id': 56014901, 'author': 'Dave Mason', 'author_id': 2961160, 'author_profile': 'https://Stackoverflow.com/users/2961160', 'pm_score': -1, 'selected': False, 'text': '<p>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 <code>JSON_VALUE</code> function. Properly formatted JSON data is required, however: strings must be enclosed in double quotes <code>"</code> and the delimiter must be a comma <code>,</code>, with the entire string enclosed in square brackets <code>[]</code>.</p>\n\n<pre><code>DECLARE @SampleString NVARCHAR(MAX) = \'"Hello John Smith"\';\n--Format as JSON data.\nSET @SampleString = \'[\' + REPLACE(@SampleString, \' \', \'","\') + \']\';\nSELECT \n    JSON_VALUE(@SampleString, \'$[0]\') AS Element1Value,\n    JSON_VALUE(@SampleString, \'$[1]\') AS Element2Value,\n    JSON_VALUE(@SampleString, \'$[2]\') AS Element3Value;\n</code></pre>\n\n<p><strong>Output</strong><br/></p>\n\n<pre><code>Element1Value         Element2Value       Element3Value\n--------------------- ------------------- ------------------------------\nHello                 John                Smith\n\n(1 row affected)\n</code></pre>\n'}, {'answer_id': 58667307, 'author': 'GBGOLC', 'author_id': 2048573, 'author_profile': 'https://Stackoverflow.com/users/2048573', 'pm_score': -1, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>DECLARE @STRING_VALUE NVARCHAR(MAX) = 'one, two,,three, four,     five'\n\nSELECT ROW_NUMBER() OVER (ORDER BY R.[index]) [index], R.[value] FROM\n(\n    SELECT\n        1 [index], NULLIF(TRIM([value]), '') [value] FROM STRING_SPLIT(@STRING_VALUE, ',') T\n    WHERE\n        NULLIF(TRIM([value]), '') IS NOT NULL\n) R\n</code></pre>\n"}, {'answer_id': 60353609, 'author': 'Eralper', 'author_id': 832991, 'author_profile': 'https://Stackoverflow.com/users/832991', 'pm_score': -1, 'selected': False, 'text': '<p>If you check the following SQL tutorial on <a href="http://www.kodyaz.com/t-sql/scalar-and-tablue-valued-user-defined-sql-split-function.aspx" rel="nofollow noreferrer">splitting string using SQL</a>, you will find a number of functions that can be used to split a given string on SQL Server</p>\n\n<p>For example, <strong>SplitAndReturnNth</strong> UDF function can be used to split a text using a separator and return the Nth piece as the output of the function</p>\n\n<pre><code>select dbo.SplitAndReturnNth(\'Hello John Smith\',\' \',2)\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/2jEe1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2jEe1.png" alt="enter image description here"></a></p>\n'}, {'answer_id': 67416730, 'author': 'Ludovic Aubert', 'author_id': 3046585, 'author_profile': 'https://Stackoverflow.com/users/3046585', 'pm_score': 0, 'selected': False, 'text': '<pre><code>CREATE TABLE test(\n    id int,\n    adress varchar(100)\n);\nINSERT INTO test VALUES(1, \'Ludovic Aubert, 42 rue de la Victoire, 75009, Paris, France\'),(2, \'Jose Garcia, 1 Calle de la Victoria, 56500 Barcelona, Espana\');\n\nSELECT id, value, COUNT(*) OVER (PARTITION BY id) AS n, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS rn, adress\nFROM test\nCROSS APPLY STRING_SPLIT(adress, \',\')\n</code></pre>\n<p><a href="https://i.stack.imgur.com/PZUGZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PZUGZ.png" alt="enter image description here" /></a></p>\n'}, {'answer_id': 70196925, 'author': 'Salman A', 'author_id': 87015, 'author_profile': 'https://Stackoverflow.com/users/87015', 'pm_score': 2, 'selected': False, 'text': "<p>In Azure SQL Database (based on Microsoft SQL Server but not exactly the same thing) the signature of <code>STRING_SPLIT</code> function looks like:</p>\n<pre><code>STRING_SPLIT ( string , separator [ , enable_ordinal ] )\n</code></pre>\n<p>When <code>enable_ordinal</code> flag is set to 1 the result will include a column named <code>ordinal</code> that consists of the 1‑based position of the substring within the input string:</p>\n<pre><code>SELECT *\nFROM STRING_SPLIT('hello john smith', ' ', 1)\n\n| value | ordinal |\n|-------|---------|\n| hello | 1       |\n| john  | 2       |\n| smith | 3       |\n</code></pre>\n<p>This allows us to do this:</p>\n<pre><code>SELECT value\nFROM STRING_SPLIT('hello john smith', ' ', 1)\nWHERE ordinal = 2\n\n| value |\n|-------|\n| john  |\n</code></pre>\n<hr />\n<p>If <code>enable_ordinal</code> is not available then there is a trick <em><strong>which assumes that the substrings within the input string are unique</strong></em>. In this scenario, <code>CHAR_INDEX</code> could be used to find the position of the substring within the input string:</p>\n<pre><code>SELECT value, ROW_NUMBER() OVER (ORDER BY CHARINDEX(value, input_str)) AS ord_pos\nFROM (VALUES\n    ('hello john smith')\n) AS x(input_str)\nCROSS APPLY STRING_SPLIT(input_str, ' ')\n\n| value | ord_pos |\n|-------+---------|\n| hello | 1       |\n| john  | 2       |\n| smith | 3       |\n</code></pre>\n"}, {'answer_id': 70970351, 'author': 'Josef B.', 'author_id': 8043027, 'author_profile': 'https://Stackoverflow.com/users/8043027', 'pm_score': 0, 'selected': False, 'text': '<p>Modified function of @Aaron Bertrand</p>\n<pre><code>CREATE FUNCTION [dbo].[SplitString]\n(\n    @List NVARCHAR(MAX),\n    @Delim VARCHAR(255),\n    @Idx int\n)\nRETURNS NVARCHAR(1000)\nAS\nBEGIN\n    DECLARE @ValueTable TABLE(String NVARCHAR(50), Ind int)\n    DECLARE @Value NVARCHAR(50)\n    BEGIN\n    INSERT INTO @ValueTable\n    SELECT Value, idx FROM\n        (SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM \n              ( \n                SELECT n = Number, \n                [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],\n                CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))\n                FROM    \n                        (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)\n                         FROM sys.all_objects) AS x\n                WHERE Number &lt;= LEN(@List)\n                AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim\n              ) AS y\n          ) AS R WHERE idx = @Idx\n    SET @Value = (SELECT String FROM @ValueTable)\n    END\n    RETURN @Value\nEND\nGO\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/2647', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/383/']