How to split comma separated string values into columns ?

Question

SELECT id, Name, value Name FROM tblRegister CROSS APPLY STRING_SPLIT(Name, ','); I Got this Error Msg 208, Level 16, State 1, Line 1 Invalid object name 'STRING_SPLIT'.

Rijwan Ansari · Answer

Can you share me exact sample data with columns name? I don't see any comma in your name column value.

Rijwan Ansari · Answer

Hi, You can try someting like this. SELECT id, Name , ISNULL ([phn1], '' ) AS [phn1], ISNULL ([phn2], '' ) AS [phn2], ISNULL ([phn3], '' ) AS [phn3], ISNULL ([phn4], '' ) AS [phn4] FROM ( SELECT id, Name , 'Phn' + CAST (ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS VARCHAR ) AS Col, Split.value FROM dbo.tblRegister AS Reg CROSS APPLY String_split( Name , ',' ) AS Split ) AS tbl Pivot ( Max (Value) FOR Col IN ([Name1],[Name2],[Name3],[Name4]) ) AS Pvt https://sqlskull.com/2020/05/10/how-to-split-comma-separated-string-values-into-columns/

Rijwan Ansari · Answer

I sent you a message. Please check.

Guest User · Answer

Can i contact fb

Rijwan Ansari · Answer

Hi As per error, there is compatibiltiy issue. You need to change the compatibility level of your database. Try below query ALTER DATABASE [Database_Name] SET COMPATIBILITY_LEVEL = 150 Then run the actual query again.

Sachin Singh · Answer

if number of table column and string in csv are equal INSERT INTO YourTableName SELECT * FROM STRING_SPLIT(YourString, ',' )

Guest User · Answer

SELECT id, Name, '' as Nam1 ,'' as Nam2,'' as Nam3 from tblRegister Select id, Name, ISNULL([Nam1],'') AS [Nam1], ISNULL([Nam2],'') AS [Nam2], ISNULL([Nam3],'') AS [Nam3] From ( Select id, Name, 'Nam'+CAST(ROW_NUMBER()OVER(PARTITION BY id order by id) as int) as Col, Split.value From tblRegister AS REG Cross apply string_split(Name,',') AS Split) AS Tbl pivot (MAX(value) For Col IN ([Nam1],[Nam2],[Nam3]) ) AS Pvt (1 row affected) Msg 208, Level 16, State 1, Line 5 Invalid object name 'string_split'. Completion time: 2022-03-04T23:34:31.3593222+05:00

Guest User · Answer

Id Name colums Value of colums name Shoaib,aziz,ali

Guest User · Answer

Rijwan Ansari Yes Like this how can i do

Nitin Sontakke · Answer

What is the version of SQL Server you are using? If I remember it correctly, I believe you have mentioned 2008 on some other thread. This function was made available on in 2012. Please read carefully following link for an alternation approaches: https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012

Muhammad Imran Ansari · Answer

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. Make sure that the your database compatibility level should be 130. To check your database compatibility level run the following command: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName' ; To set your database compatibility level, use the following command: ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 130

How to split comma separated string values into columns ?

Answers (11)