Removing duplicates from an array populated by a reader

Question

I have a database column that has several names separated by commas in each row. I'm trying to populate a data-table containing all the names but without duplicates.

Currently the list populates but duplicate names still appear.

My code is:

while (reader.Read())
        {
            string[] array = reader[0].ToString().Split(',');
            string[] unique = array.Distinct().ToArray();
            foreach (string s in unique)
                dt.Rows.Add(s);
        }

Any help would be greatly appreciated, thanks.

what does your Query look like ..? wouldn't it be easier to alter the query to return the Distinct Rows..? — MethodMan, Feb 16 '13 at 18:04
Is the problem that different source rows can contain duplicates? For example, `"A,B,B,C"` and `"A,D,D,F,F"` adds `A`,`B`,`C`,`A`,`D`,`F` (duplicate `A`)? — dtb, Feb 16 '13 at 18:07
A distinct row would have several names separated by commas such as: name1, name2, name3. But another column may include a duplicate of a name such as: name1, name4. Name 1 will then be returned twice. — user2078938, Feb 16 '13 at 18:08
Storing lists of names in a single field is a horrible design (nothing personal). The difficulty you are having with this query is an example of the problems it can create. If you are able to, normalize your db. If you don't know how, I've heard good things about the book, Database Design for Mere Mortals. — Dan Bracuk, Feb 16 '13 at 18:12
The awkward nature is because it's user populated from a sanitized textbox to list friends in photos. It certainly wasn't an inspired way of doing things but at least it's worked out. And thanks I'll check out the book. — user2078938, Feb 16 '13 at 18:21

score 1 · Answer 1 · answered Feb 16 '13 at 18:14

Here's a database solution using CROSS APPLY:

CREATE TABLE YourTable (YourColumn varchar(100));

INSERT INTO YourTable VALUES ('John,Jack,Jill,John'), 
('Mike,John,Jack,Jill,John');

SELECT DISTINCT
   Split.a.value('.', 'VARCHAR(100)') AS UniqueName
FROM  
   (SELECT   
    CAST ('<M>' + REPLACE(YourColumn, ',', '</M><M>') + '</M>' AS XML) AS String  
    FROM  YourTable
) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a)

Produces these results:

UNIQUENAME
Jack
Jill
John
Mike

And some sample fiddle.

Thank you. This method is be good to know. I've implemented Hossein Narimani Rad's answer though and it's been an easier fix on the c# side. Thanks again. — user2078938, Feb 16 '13 at 18:18

Hossein Narimani Rad · Accepted Answer · 2013-02-16T18:12:42.280

0

Try this

List<string> distinctValues = new List<string>();

while (reader.Read())
{
    string[] unique = reader[0].ToString().Split(',').Distinct().ToArray();

    foreach (string s in unique)
        if (!distinctValues.Contains(s))
            distinctValues.Add(s);
}

foreach (string s in distinctValues)
    dt.Rows.Add(s)

edited Feb 16 '13 at 18:12

answered Feb 16 '13 at 18:08

Hossein Narimani Rad

31,361
18
86
116

Removing duplicates from an array populated by a reader

2 Answers2