REmoving duplicated within a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

REmoving duplicated within a field

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2007-07-28 : 18:27:48

Hi all,

i´m wondering how to do this.
i got a field, and in that field i have list of words ex:

house
boat
car
road
dog
sun
boat
road

i know how to do this in ASP.net code but seems a waste of resources selecting to all , to the asp and then back to sql.
must be a better way of doing this.

tnx,
Mike

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-28 : 18:46:39

Is it single column table? If not, how do you know which row should be deleted?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-28 : 18:50:19

delete f
from (select row_number() over (partition by wordcol) as recid from wordtable) as f
where recid > 1

E 12°55'05.25"
N 56°04'39.16"

mike13
Posting Yak Master

219 Posts

Posted - 2007-07-29 : 05:07:58

HI,

No row should be deleted at all, the duplicated are the content of one field, i want to remove the duplicates from that field ex:

Select word from T_words where id=10

and content of the field word has the duplicates

house
boat
car
road
dog
sun
boat
road

separated by <ENTER>

so i want to remove the duplicates, probably need to delete the content to update with the right one

tnx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-29 : 12:52:22

You know this is REALLY bad design? I feel sorry for you to maintain this application.

Make use of a CSV-SPLIT function (may replace comma with CRLF). There are many here at SQL Team. Also use the CROSS APPLY keyword.
Then select the distinct combination and put back as concatenated string.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-29 : 13:04:14

Or just plain

SELECT DISTINCT t1.PrimaryCol,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + f.Part FROM WordTable AS t2 CROSS APPLY Csv_Split(t2.PrimaryCol) AS f WHERE t2.PrimaryCol = t1.PrimaryCol ORDER BY ',' + f.Part FOR XML PATH('')), 1, 1, '') AS CODES
FROM WordTable AS t1
ORDER BY t1.PrimaryCol

E 12°55'05.25"
N 56°04'39.16"

mike13
Posting Yak Master

219 Posts

Posted - 2007-07-29 : 17:01:11

Hi Peso,

you say it is bad design having having CRLF in my DB field?
so beter replace them with commas insted of CRLF.
TO be honest now the only reason i use CRLF is because the word lists are CRLF separated i split them in my code and put them in array and use them but should matter splitting on the comma or crlf.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 02:37:25

You shouldn't be using any separator at all!
Read about normalization at http://www.datamodel.org/NormalizationRules.html

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources