Find duplicates assuming padded 0s

[Moebius01]

I have what should be a simple query to do, but for the life of me I'm stuck. Dealing with a data import from a client who converted their ID String from numeric to 6 digit numeric. (ie... record with id of 3841 is now 003841) I won't go into all the bs details of why we can't just redo the import, suffice it to say I'm stuck fixing it manually.

Basically, I need to select rows from the table where ID 3841 = ID 003841. Can someone point me in the right direction?

 

[SQLSister]

try
replicate (0, 6) + a.id1= b.id2

"NOTHING is more important in a database than integrity." ESquared

 

[Qik3Coder]

You have two options, either pad yours or chop theirs.

A:

declare @a varchar(6)
set @a = '1'
select @a As Variable, replicate('0',6) as FullReplicate, replicate('0',6-len(@a))+@a as LengthBasedReplicated_EG_Padding

B:

declare @a varchar(6)
declare @b varchar(6)
set @a = '1'
set @b = '0001'
select @a as VarA, @b as VarB, 
case when @a = @b then 'X' else '' end as VarCharCompare, 
case when cast(@a as int) = cast(@b as int) then 'X' else '' end as IntCompare

-Sometimes the answer to your question is the hack that works

 

[bborissov]

SELECT RIGHT('000000'+CAST(YourField as varchar(6)),6) AS Dbl
       FROM YOUR_TABLE
GROUP BY RIGHT('000000'+CAST(YourField as varchar(6)),6)
HAVING COUNT(*) > 1

If YourField is already vacrhar there is no need to CAST it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP