Split Comma Seperated Values into Rows with SQL Server

Question

I have a table as per below

Names

NameID |   Name  | NickNames
--------------------------
   1   | Richard | Rich, Dick
   2   |   Sam   | Samuel, Samantha, Sammy
   3   |   John  | Jonathan, Johnny

And so on...

I have an iterative function already that loops through all rows in this table, and I have the ID of the row at which the loop currently is. I would like to delete all rows from the table where the 'Name' column is included in the current rows 'NickNames' column.

For example, row 4 might be:

    4   |  Johnathan | NULL

I would like for this row to be deleted because 'Johnathan' appears in row 3's NickNames.

Essentially I am trying to select the NickNames column as a series of rows as opposed to its raw CSV-like data. Pretty much the opposite of This Blog Post

In my eyes it looks something like:

DELETE FROM Names WHERE Name IN (SELECT NickNames FROM Names WHERE NameID=@RowID);

Thanks in advance!

What should happen if "Johnny" is the Name of entry 5 with a Nickname of "John"? Should both NameID 3 "John" and NameID 5 "Johnny" be deleted? — Paul Williams
– Paul Williams, Commented Dec 3, 2015 at 2:51
@PaulWilliams In my head, entry 5 will be deleted once the loop hits entry 3 and thus never matter :) — Sean Missingham
– Sean Missingham, Commented Dec 3, 2015 at 2:54

Gordon Linoff · Accepted Answer · 2015-12-03 02:57:37Z

1

You don't need a loop to do this. I admit the following may not be the fastest, but it is a single statement:

delete from names n
    where exists (select 1
                  from names n2
                  where ',' + n2.nicknames + ',' like '%,' + n.name + ',%' and
                        n2.nameid <> n.nameid
                 );

Note: you shouldn't be storing lists of things in a comma-delimited list. Relational databases should have only one value per column. A junction table is the right approach.

answered Dec 3, 2015 at 2:57

Gordon Linoff

1.3m62 gold badges704 silver badges856 bronze badges

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

1 Comment

Sean Missingham Over a year ago

Thanks Gordon, that works better than I'd hoped for. I'm aware of the inherent issues with storing comma-delimited values in a single column but in this case because of my db it's a much cleaner approach and it shouldn't cause performance issues due to ridiculous number of table scans as I won't really use it that way

Collectives™ on Stack Overflow

Split Comma Seperated Values into Rows with SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related