CODEWITHSUNDEEP
sqlsql-server
New_here
New_here

Reputation: 85

Split rows based on a delimiter SQL Server

How to split a col in sql server based on a delimiter?

I tried using STRING_SPLIT() function:

select * 
from TABLE1 
CROSS APPLY STRING_SPLIT(city, ',' ).

I was getting a invalid object name string_split error. I don't want to use a complex procedure to solve this as it's a one time thing. Are there other better methods or functions catered for such purposes in SQL Server?

Input: TABLE1

name   | age | city         | dept
RACHEL | 43  | NJ, WH       | AUTO
Rob    | 12  | NY, CALI, TX | WEB
Ashley | 23  | CN           | KYO
Laine  | 22  |              | KK
Reeza  | 45  | NULL         | AUTO

Output:

nam e  | age | city | dept
RACHEL | 43  | NJ   | AUTO
RACHEL | 43  | WH   | AUTO
Rob    | 12  | NY   | WEB
Rob    | 12  | CALI | WEB
Rob    | 12  | TX   | WEB
Ashley | 23  | CN   | KYO
Laine  | 22  |      | KK
Reeza  | 45  | NULL | AUTO

Upvotes: 0

Views: 502

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use string_split():

select t.name, t.age, trim(s.value) as city, t.dept
from t cross apply
     string_split(t.city, ',') s;

I'm not sure why this would produce an error. A more complex method uses a recursive CTE:

with cte as (
      select t.name, t.age, convert(varchar(max), NULL) as city, t.dept, 1 as lev, convert(varchar(max), t.city) as rest
      from t
      union all
      select name, age, trim(left(rest, charindex(',', rest + ',') - 1)),
             dept, lev + 1, stuff(rest, 1, charindex(',', rest + ','), '')
      from cte
      where rest <> ''
     )
select name, age, city, dept
from cte
where city is not null;

Here is a SQL Fiddle.

Upvotes: 1

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

It seems that your SQL Server version is below 2016. That's why the STRING_SPLIT() function is not supported.

SELECT @@VERSION; will help to identify the version.

Here is another approach based on XML and XQuery. It will work on SQL Server 2008 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [name] VARCHAR(20), age INT, city VARCHAR(100), dept VARCHAR(10));
INSERT INTO @tbl (name, age, city, dept) VALUES
('RACHEL', 43 , 'NJ, WH','AUTO'),
('Rob', 12 ,'NY, CALI, TX','WEB '),
('Ashley', 23 , 'CN','KYO'),
('Laine', 22 , '', 'KK'),
('Reeza', 45 , NULL,'AUTO');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT *
      , CAST('<root><r><![CDATA[' + 
         REPLACE(SPACE(1) + city, @separator, ']]></r><r><![CDATA[') + ']]></r></root>' AS XML) AS xmldata
    FROM @tbl
)
SELECT id, name, age
      , LTRIM(c.value('(./text())[1]','VARCHAR(100)')) AS city
      , dept
FROM rs
    OUTER APPLY xmldata.nodes('/root/r') t(c);

Output

+----+--------+-----+------+------+
| id |  name  | age | city | dept |
+----+--------+-----+------+------+
|  1 | RACHEL |  43 | NJ   | AUTO |
|  1 | RACHEL |  43 | WH   | AUTO |
|  2 | Rob    |  12 | NY   | WEB  |
|  2 | Rob    |  12 | CALI | WEB  |
|  2 | Rob    |  12 | TX   | WEB  |
|  3 | Ashley |  23 | CN   | KYO  |
|  4 | Laine  |  22 |      | KK   |
|  5 | Reeza  |  45 | NULL | AUTO |
+----+--------+-----+------+------+

Upvotes: 2

Related Questions