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

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 |

@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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related