I've got a better function than SOUNDEX for you. It's called "StringsClose" and it compares strings while forgiving one and only one typo. A typo is defined as a mistyped character, an extra character, an omitted character or a single transposition of two characters. It is not perfect, and really shouldn't be trusted where both strings are 1-5 characters. All disclaimers aside, it will still handle what you're asking for in your examples.

Before comparing the strings in your example, you will probably want to get rid of all periods (and probably other special characters as well). Like this:

d eclare @StringA varchar(50) = 'A.B.C. Movers'

,@StringB varchar(50) = 'ABC Movres'

select @StringA as [@StringA], @StringB as [@StringB], sc.StringsClose

from dbo.itvf_StringsClose(replace(@StringA,'.',''),

replace(@StringB,'.','')

) sc

The code for the function follows. It looks clunky but runs fast enough that I've never had to revisit it and make it more elegant. Let me know if this works for you, or if you have any questions.

C REATE FUNCTION [dbo].[itvf_StringsClose]

(

@String1 varchar(50)

,@String2 varchar(50)

)

RETURNS TABLE

AS

-- =================================================================================================

-- Author:Bob Hovious

-- Create date: 3/19/2008

-- Description:This function compares two Strings and deems them to be "close" if:

--1. One has one more character than the other, in any position

--2. One character has been mistyped

--3. Two characters are transposed

-- =================================================================================================

RETURN

(

WITH

-- remove all blanks from Strings

trimmedStrings AS (SELECT replace(@String1,' ','') AS String1

,replace(@String2,' ','') AS String2)

-- make sure that the longest String is always put in String1

-- if they are 2 or more apart they aren't close, so don't bother passing rows to the "stuffing cte

,basevalues AS

(SELECT CASE WHEN len(String1) >= len(String2) THEN String1 ELSE String2 END AS String1 -- long String 1

,CASE WHEN len(String1) >= len(String2) THEN String2 ELSE String1 END AS String2 -- short String 2

FROM trimmedStrings

WHERE abs(len(String1)-len(String2)) < 2

)

,tally1 AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),

(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),

(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),

(41),(42),(43),(44),(45),(46),(47),(48),(49),(50)) v (N1)

)

,tally2 AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),

(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),

(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),

(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),

(41),(42),(43),(44),(45),(46),(47),(48),(49),(50)) v (N2)

)

----------------------------------------------------------------------------------------

-- build sets of character Strings for "Like" comparisons and transposition comparisons

----------------------------------------------------------------------------------------

,comparisons AS (-- if they're equal, they're equal

SELECT TOP (1) 'Y' AS StringsClose

FROM basevalues

WHERE String1 = String2

UNION ALL

-- replace each character in String1 with a wildcard to handle single keystroke typos

-- whether wrong character typed or single character omitted

-- this works whether or not the length of String1 is greater than String2

SELECT TOP (1) 'Y' AS StringsClose

FROM basevalues

CROSS JOIN tally1 t1

WHERE 1 = 1

AND String2 LIKE stuff(String1,N1,1,'%')

AND N1 BETWEEN 2 and len(String1)

UNION ALL

-- where Strings are the same length, check for transposition

SELECT TOP (1) 'Y' AS Stringsclose

FROM basevalues

CROSS JOIN tally2 t2

WHERE 1 = 1

AND len(String1) = len(String2)

AND N2 < len(String1)

AND String2 = stuff(String1,N2,2,reverse(subString(String1,N2,2)))

)

SELECT isnull((SELECT TOP (1) StringsClose FROM comparisons),'N') AS StringsClose

)

Edited to add: Having re-read and thought about your problem, this may not be much help as it would run slow. It runs fast as written because it compares two strings almost instantly. In your case, you would have to adapt it to run against your table of correct values for each of the string one "LIKE" combinations generated by the function. That would NOT run almost instantly.

The deciding factor is how often you hit these misspellings. If it's one time in a thousand, this approach might be workable for you.

How many rows are in the table with correct values? I assume you have an index over the column with the correct values. What is the length of that column?