Duplicate Text data

Anyone have a method for identifiying dupes in a text field?
coromokes wrote:
> Anyone have a method for identifiying dupes in a text field?
By dupes, you mean the existence of the same text value in more than one
row? If so, you can group on the text column and use a having clause to
test for dupes.
You'll need to convert to a varchar or nvarchar data type first, which
means you'll only get access to teh first 4,000 or 8,0000 characters for
the test. But maybe that's good enough.
Select CAST(TextDataCol as VARCHAR(8000))
From TableName
Group By CAST(TextDataCol as VARCHAR(8000))
Having COUNT(*) > 1
David Gugick
Imceda Software
www.imceda.com
sql

Dupes with NULL

"I am trying to select all of the duplicate values out of a table like this:

field1 field2 field3 field4 field5
jason anderson 266985421 Florida NULL
Derek Lee 56898755 Louisiana 32
jason anderson 266985421 Florida NULL

the first and third are duplicate records but with the sql I have so far it doesn't reconize them as dupes becuase of the NULL.

SELECT field1, field2, field3, field4, field5
FROM table
WHERE ((([field1]) In
(SELECT [field1]
FROM [table] AS TMP
GROUP BY field1, field2, field3, field4, field5
HAVING Count(*)>1 And [field1] = [table].[field1] AND .....and field5 = table.field5 )))
ORDER BY field1,field2, ......field5

what do I do???????"select f1,f2,f3,f4,f5,count(*) from table group by f1,f2,f3,f4,f5
having count(*) > 1

this will give all duplicate records plus count of them

best of luck|||I forgot one specific part...........there is a 6th field, and the 6th field is not one of the fields that can have a dupe, but i have to display it in the output

field1 field2 field3 field4 field5 field6
jason anderson 266985421 Florida NULL programmer
Derek Lee 56898755 Louisiana 32
jason anderson 266985421 Florida NULL dba

so the output must display like this since this is a dupe record
field1 field2 field3 field4 field5 field6
jason anderson 266985421 Florida NULL programmer
jason anderson 266985421 Florida NULL dba

.......I have to show both fields|||if you add f6 in the query , desired output will be generated|||I tryed adding f6 to the query and it did not work...the query also will not show BOTH the dupe records, and that is what I need, I need it to show the all 6 fields even though 5 of them have to match, the 6th field does not have to match. so my ouput needs to be as such......

fied1 field2 field3 field 4 field 5 field6
match match match match match match doesn't matter
match match match match match match doesn't matter

.........and my other probelm was that if the field was a null then it would not count as a dupe........do you have any suggestions|||Try this

select a.f1, a.f2,a.f3,a.f4,f5=ISNULL(a.f5,'OOPS') ,b.f6
FROM (select f1, f2,f3,f4,f5=ISNULL(f5,'OOPS'),cc=count(*) from #temp group by f1, f2,f3,f4,f5=ISNULL(f5,'OOPS') having count(*) > 1) AS A,
(select f1, f2,f3,f4,f5=ISNULL(f5,'OOPS'),f6 from #temp ) AS B
where a.f1 = b.f1 and
a.f2 = b.f2 and
a.f3 = b.f3 and
a.f4 = b.f4 and
a.f5 = b.f5

NULL is hanndled by converting it to 'OOPS' , if you don't like OOPS in your output U can use CASE statement to convert it back to null in FIRST SELECT statement

In case f6 is also having Dup then use Distinct in first select statement

Dupes

ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.
Try:
delete MyTable
where exists
(
select
*
from
MyTable m
where
m.CID = MyTable.CID
and
m.[Date] = MyTable.CID
and
m.ID > MyTable.CID
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.
|||Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:

> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>
|||On SQL Server 2005 you can do:
;WITH Dups (seq)
AS
(SELECT ROW_NUMBER() OVER(
PARTITION BY cid, date
ORDER BY id)
FROM Foo)
DELETE Dups
WHERE seq > 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Oops! God catch.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:D180E323-D5DE-4B45-A9D8-156EDC31FED4@.microsoft.com...
Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:

> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>

Dupes

ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.Try:
delete MyTable
where exists
(
select
*
from
MyTable m
where
m.CID = MyTable.CID
and
m.[Date] = MyTable.CID
and
m.ID > MyTable.CID
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.|||Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:
> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>|||On SQL Server 2005 you can do:
;WITH Dups (seq)
AS
(SELECT ROW_NUMBER() OVER(
PARTITION BY cid, date
ORDER BY id)
FROM Foo)
DELETE Dups
WHERE seq > 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Oops! God catch.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:D180E323-D5DE-4B45-A9D8-156EDC31FED4@.microsoft.com...
Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:
> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>sql