CODEWITHSUNDEEP
sqlsql-server
Sean Missingham
Sean Missingham

Reputation: 938

Split Comma Seperated Values into Rows with SQL Server

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!

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

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.

Upvotes: 1

Related Questions