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).