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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-23 : 08:37:25
|
| Jason writes "I have a problem very similar to one you have already answered. 'Is there a SQL that I can use to delete duplicate entries from a data store, while leaving a distinct copy - leave a single copy, remove all duplicate except one?' Mine has a twist. I have a table:CREATE TABLE "dbo"."UserSurveyInfo" ( "UserId" "varchar" (10) NOT NULL , "SurveyId" "int" NOT NULL , "TimeEntered" "datetime" NULL, CONSTRAINT "PK_UserSurveyInfo" PRIMARY KEY CLUSTERED ( "SurveyId", "UserId" ))And nother table:CREATE TABLE "dbo"."Sessions" ( "SessionId" "char" (23) NOT NULL , "UserId" "char" (10) NOT NULL , "LastUsedTime" "datetime" NOT NULL , "Value1" "varchar" (255) NULL , "Value2" "varchar" (255) NULL , "Value3" "varchar" (255) NULL , "Value4" "varchar" (255) NULL , "Value5" "varchar" (255) NULL , "Value6" "varchar" (255) NULL , "Value7" "varchar" (255) NULL , "Value8" "varchar" (255) NULL , "Value9" "varchar" (255) NULL , "Value10" "varchar" (255) NULL, CONSTRAINT "PK_Sessions" PRIMARY KEY NONCLUSTERED ( "SessionId" ))The problem is UserId in Sessions was created as a char(10) and not varchar(10) so now rows exsit in UserSurveyInfo that have 2 trailing spaces. If you select from UserSurveyInfo using a simple where clause, it is almost random as to which it returns, with the spaces or without.I've tried using RTRIM and Like to find the dups, but my results never seem to be correct. This is SQL 6.5 sp6a, NT 4.0 sp5a. It has cropped up during my 2000 upgrade testing. 2000 seems to see them as actual dups, where 6.5 did not. After the upgrade I plan to set UserId to varchar(10) across the board.Thanks!Jason." |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-23 : 09:55:16
|
| you can follow Graz article on it http://www.sqlteam.com/item.asp?ItemID=3331try using ltrim(rtrim(fieldname)) on sessionidHTH--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
|
|
|
|
|
Almost dup rows