Robin Hames

Hints, tricks and tips relating to MS SQL Server and .NET
posts - 14 , comments - 44 , trackbacks - 0

Thursday, October 30, 2008

SQL Server CHECKSUM() with Single Quotes (‘) gives duplicate values.

SQL Server CHECKSUM() with Single Quotes (‘) gives duplicate values.
 
A database I have been working on uses a checksum across address data to try to establish whether an entered address already exists in the database.
A problem occurred because two addresses had the same checksum value, but the actual address was subtlety different. The difference was that one had the street address as “St James’ Street”, whereas the other had “St James Street”.
The following test script shows that the single quote does not appear to alter the checksum calculation:
declare @t1 table (s varchar(50))
insert into @t1 values ('10 St James Street')
insert into @t1 values ('10 St James'' Street')
insert into @t1 values ('1''0'' S''t'' J''a''m''e''s'' S''t''r''e''e''t')
 
select checksum(s), * from @t1
 
These three rows all give the same checksum value (178448437).

Posted On Thursday, October 30, 2008 10:58 AM | Comments (4) | Filed Under [ SQL Server ]

Powered by: