I've come up with the following T-SQL to compare similar same length strings.
Example usage would be:
- OCR returns a value which is expected to be in the database.
- PATINDEX is used to check the value's in the database, including matching for common errors (e.g. 0/O, I/1/l).
- If multiple values are found, each match is scored for likeness to the original.
declare @t table (solution nvarchar(32), score bigint) insert @t(solution) select 'Portugal' union select '9ortugal' union select '9ortu8al' union select 'Portu8al' union select 'P0rtugal' union select '90rtugal' union select '90rtu8al' union select 'P0rtu8al' union select 'Portuga1' union select '9ortuga1' union select '9ortu8a1' union select 'Portu8a1' union select 'P0rtuga1' union select '90rtuga1' union select '90rtu8a1' union select 'P0rtu8a1' declare @answer nvarchar(32) = 'Portugal' update @t set score = cast(cast(cast(@answer as varchar) as varbinary(max)) as bigint) - (cast(cast(cast(solution as varchar) as varbinary(max)) as bigint) & cast(cast(cast(@answer as varchar) as varbinary(max)) as bigint)) select * from @t order by score --first result is the most similar
solution score Portugal 0 Portuga1 76 Portu8al 4653056 Portu8a1 4653132 P0rtugal 22236523160141824 P0rtuga1 22236523160141900 P0rtu8al 22236523164794880 P0rtu8a1 22236523164794956 9ortugal 4611686018427387904 9ortuga1 4611686018427387980 9ortu8al 4611686018432040960 9ortu8a1 4611686018432041036 90rtugal 4633922541587529728 90rtuga1 4633922541587529804 90rtu8al 4633922541592182784 90rtu8a1 4633922541592182860
- Conversion to varchar because nvarchar seems to cause a loss of precision
- Conversion to varbinary because doing varchar straight to numeric would result in a non-numeric issue for non-numeric characters.
- Conversion to bigint to allow bitwise & arithmetic comparison
- Use of - and & may be superfluous; perhaps just the
-would be sufficient, but I feel (superstitious guess/don't have a strong enough understanding) that the
- Errors at the start of the string have more impact than those later in the string.
- Results look quite good on my test data, but not sure how predictable this would be in reality.
- It would be nice to be able to weight the errors; e.g. O for 0 is very common; 6 for G or 8 for g less so; so the former should have a greater weighting (i.e. P0rtugal should be preferential to Portu8al; not just because of the character's position in the string).