CODEWITHSUNDEEP
sqlsql-server-2012
user1411074
user1411074

Reputation: 13

SQL - Parse String

I have a table that contains: 

ID     Names 
1      Aaron, Betsy, Cindy 
2      Dillon, Eric, Fred

I would like to parse through the name column and have it return: 

ID   Names 
1    Aaraon 
1    Betsy 
1    Cindy 
2    Dillon

I have found several functions online that parse the names column but does not tie the ID back to it.

Upvotes: 0

Views: 457

Answers (1)

Taryn
Taryn

Reputation: 247860

How about something like this:

;with cte (id, name, names) as
(
  select id,
    cast(left(names, charindex(',',names+',')-1) as varchar(50)) name,
         stuff(names, 1, charindex(',',names+','), '') names
  from yourtable
  union all
  select id,
    cast(left(names, charindex(',',names+',')-1) as varchar(50)) name,
    stuff(names, 1, charindex(',',names+','), '') names
  from cte
  where names > ''
) 
select id, name
from cte
order by id

See SQL Fiddle with Demo

Returns the result:

| ID |   NAME |
---------------
|  1 |  Aaron |
|  1 |  Betsy |
|  1 |  Cindy |
|  2 | Dillon |
|  2 |   Eric |
|  2 |   Fred |

Upvotes: 2

Related Questions