STRING_SPLIT in SQL Server 2012

split comma separated string in sql server 2012
string_split alternative
invalid object name 'string_split'.
'string_split' is not a recognized built-in function name.
sql split string by character
how to split a comma-separated value to columns in sql server
sql server split string into rows
sql split string by space

I have this parameter

@ID varchar = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' 

I want to do something to split the comma-separated values.

The string_split function doesn't work and I get this error:

The STRING_SPLIT function is available only under compatibility level 130

and I try to alter my database and set the compatibility to 130 but I don't have a permission for this change.

Other approach is too use XML Method with CROSS APPLY to split your Comma Separated Data :

SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
    SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a);

Result :

DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Example :

DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id    NVARCHAR(300),
 marks NVARCHAR(300)
); 
--insert into @StudentsMark 
;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a))
     INSERT INTO @StudentsMark
            SELECT C.id,
                   C1.marks
            FROM CTE C
                 LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
SELECT *
FROM @StudentsMark;

STRING_SPLIT in SQL Server 2012, This article will cover the STRING_SPLIT function in SQL Server ALTER DATABASE AdventureWorks2012 SET compatibility_LEVEL=120. eTour.com is the newest place to search, delivering top results from across the web. Content updated daily for server 2012.

Inline function based on Yogesh Sharma and Salman A answers:

Create FUNCTION [dbo].[fn_split_string]
(
    @string    nvarchar(max),
    @delimiter nvarchar(max)
)
/*
    The same as STRING_SPLIT for compatibility level < 130
    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
*/
RETURNS TABLE AS RETURN
(
    SELECT 
      --ROW_NUMBER ( ) over(order by (select 0))                            AS id     --  intuitive, but not correect
        Split.a.value('let $n := . return count(../*[. << $n]) + 1', 'int') AS id
      , Split.a.value('.', 'NVARCHAR(MAX)')                                 AS value
    FROM
    (
        SELECT CAST('<X>'+REPLACE(@string, @delimiter, '</X><X>')+'</X>' AS XML) AS String
    ) AS a
    CROSS APPLY String.nodes('/X') AS Split(a)
)

Example:

DECLARE @ID NVARCHAR(300)= 'abc,d,e,f,g';
select * from fn_split_string(@ID,',')

The STRING_SPLIT function in SQL Server, Transact-SQL reference for the STRING_SPLIT function. This table-valued function splits a string into substrings based on a character delimiter. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command: ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Note that compatibility level 120 might be default even in new Azure SQL Databases.

For reference:

Version - Highest Compatibility Level - Lowest Available Level

SQL 2017 - 140 - 100 SQL 2016 - 130 - 100 SQL 2014 - 120 - 100 SQL 2012 - 110 - 90 SQL 2008 - 100 - 80 SQL 2005 - 90 - 80 SQL 2000 - 80 - 80

Also, check your syntax as well like:

SELECT Value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

STRING_SPLIT (Transact-SQL), Here are the contenders I am going to compare (using SQL Server 2012, 11.00.​2316, on a Windows 7 VM with 4 CPUs and 8 GB of RAM):  The advantage of this function over the Microsoft's STRING_SPLIT function, apart from it working on SQL Server 2012, is it also allows the string delimiter to be more than one character in length.

Another approach would be to use CHARINDEX and SUBSTRING in a WHILE:

DECLARE @IDs VARCHAR(500);
DECLARE @Number VARCHAR(500);
DECLARE @charSpliter CHAR;

SET @charSpliter = ',';
SET @IDs = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' + @charSpliter;

WHILE CHARINDEX(@charSpliter, @IDs) > 0
BEGIN
    SET @Number = SUBSTRING(@IDs, 0, CHARINDEX(@charSpliter, @IDs));
    SET @IDs = SUBSTRING(@IDs, CHARINDEX(@charSpliter, @IDs) + 1, LEN(@IDs));

    PRINT @Number;

END;

Split strings the right way - or the next best way, Here Mudassar Ahmed Khan has shared with an example, a function to split a string in SQL Server 2005, 2008 and 2012 versions. The string  STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function. To change the compatibility level of a database, refer to View or Change the Compatibility Level of a Database.

For those looking how to transform multi-line text into rows, here is a code based on the answer:

declare @text varchar(max) = 'line0
line1
line2';

select split.a.value('.', 'nvarchar(max)') data
from
(
    select cast('<x>' + replace(@text, char(13) + char(10), '</x><x>') + '</x>' as xml) as string
) as a
cross apply string.nodes('/x') as split(a);

Split function in SQL Server Example: Function to Split Comma , He really liked the new feature of SQL Server 2016 and decided to start The new function STRING_SPLIT is introduced in SQL Server 2016 and if being verbose is not a trick, I learned to solve… June 28, 2012. In "SQL"  Introduction to SQL Server STRING_SPLIT() function The STRING_SPLIT() function is a table-valued function that splits a string into a table that consists of rows of substrings based on a specified separator. The following shows the syntax of the STRING_SPLIT() function: STRING_SPLIT (input_string, separator)

SQL SERVER - Fix Error: Invalid object name STRING_SPLIT, This article explains how we can split a delimited string in Sql Server without needing to create a STRING_SPLIT function in Sql Server 2016 This article will cover the STRING_SPLIT function in SQL Server including an overview and detailed usage examples. SQL Server users usually need string parsing and manipulation functions. In several scenarios, this string parsing or manipulation operation can be very painful for developers or database administrators.

How to Split a String in Sql Server without using a function, This tutorial shows you how to use the SQL Server STRING_SPLIT() function to split a string into a row of substrings based on a specified separator. Here Mudassar Ahmed Khan has shared with an example, a function to split a string in SQL Server 2005, 2008 and 2012 versions. The string containing words or letters separated (delimited) by comma will be split into Table values. He has also explained how to use the Split function to split a string in a SQL Query or Stored Procedures in SQL Server 2005, 2008 and 2012 versions. TAGs: SQL Server

SQL Server STRING_SPLIT Function, New in-built Table-Valued Function STRING_SPLIT() in SQL Server 2016 – to split strings. Till now it was bit tricky to split a Sentence or CSV  Here are the contenders I am going to compare (using SQL Server 2012, 11.00.2316, on a Windows 7 VM with 4 CPUs and 8 GB of RAM): CLR If you wish to use CLR, you should definitely borrow code from fellow MVP Adam Machanic before thinking about writing your own (I've blogged before about re-inventing the wheel , and it also applies to free code

Comments
  • Here is somewhat old-fashioned solution stackoverflow.com/questions/697519/…
  • Worked great except it was returning two columns and I wanted to use it in an IN so I needed just the values. To really match the new built-in function it should only return the values, so I removed the id from the select statement in the function body.
  • @CalebMauer, yes, in case you need exactly string_split functionality, you have to comment id row. id is usefull/comfortable when you have to insert into one table two columns from two strings ('a1,b1,c1' to Column1 and 'a2,b2,c2' to Column2 for instance).
  • I try to alter my database but I don't have the permeation to do this change. iam using sql server 2012 and I check my syntak there is no error.
  • No problem, check this out: docs.microsoft.com/en-us/sql/relational-databases/databases/…
  • Also, in order to alter stuff on your db, you require ALTER permissions.
  • In order to grant permissions to user: docs.microsoft.com/en-us/sql/t-sql/statements/…
  • You are not answering the question