Deleting duplicates when table contains Text 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 2000 Forums

Transact-SQL (2000)

Deleting duplicates when table contains Text Field

Author

Topic

mj76
Starting Member

8 Posts

Posted - 2002-04-18 : 09:44:02

I have a table that contains dup's however, the problem I've found, is that If I try to a SELECT DISTINCT(*) on the table, it doesn't work, because there is a text field in the table.

I tried the solution on this site prior to posting here, however, as i mentioned aove, it won't work because of the text field.

If anyone has any experience with this, I would greatly appreciate any help, guidance, or direction you are willing to share.

Thanks

mj

jongregg
Starting Member

31 Posts

Posted - 2002-04-18 : 10:10:23

Does your table have a unique identifier for each of the rows regardless of whether it is a duplicate row or not?

Is the data in the text field duplicated as well?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-18 : 10:12:09

You could do it by using the CONVERT function to convert the text to a large varchar field before running the query...

SELECT CONVERT(varchar(8000), textfield), Count(*)
FROM tablename
GROUP BY CONVERT(varchar(8000), textfield)

I've used a varchar(8000) here, but the length will depend on the maximum length of the text values.

It's probably not an ideal way to do it, but it works ok for me

Tim

mj76
Starting Member

8 Posts

Posted - 2002-04-18 : 10:25:05

Tim,
Your concept could possibly work, however there are often times when the field will hold more than 8k characters, as it is logging instant messages, and sometimes these chats can be quite long. I'll give it a shot though and see what comes of it.

Thanks

mj

jongregg
Starting Member

31 Posts

Posted - 2002-04-18 : 15:26:10

If that doesn't work you could always:

SELECT DISTINCT
COL1,
COL2,
etc (except the text field)
INTO
TEMP_TABLE
FROM
TABLE

Then run:

ALTER TEMP_TABLE ADD colx TEXT

UPDATE TEMP_TABLE
SET TEMP_TABLE.colx = TABLE.colx
FROM TABLE INNER JOIN TEMP_TABLE ON TABLE.UNIQUE_KEY = TEMP_TABLE.UNIQUE_KEY

TRUNCATE TABLE

INSERT INTO TABLE
SELECT * FROM TEMP_TABLE

DROP TEMP_TABLE

Cheers
Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources