SQL - Parse String

Question

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.

Is there a particular reason you don't already have a one-to-many relationship modeled? Or is this query part of your plan to migrate to a proper one-to-many relationship? — cdhowie
– cdhowie, Commented Jan 4, 2013 at 0:08
When confronted by problems like this and there isn't a function in the rdbms you are using, you can either write that function yourself or bring the result set into your client program and post-process the results locally. For small data sets, I sometimes reach for the latter since it can be simpler. — Paul McNett
– Paul McNett, Commented Jan 4, 2013 at 0:30
What's the example that you found that returns the names but not the id? — Greg
– Greg, Commented Jan 4, 2013 at 0:30

Taryn · Accepted Answer · 2013-01-04 01:11:25Z

2

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 |

answered Jan 4, 2013 at 1:11

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taryn Over a year ago

@user1411074 Happy to help. If this answer is helpful to you then be sure to accept via the checkmark on the left. It will help future visitors to the site and you get reputation for accepting.

Collectives™ on Stack Overflow

SQL - Parse String

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related