OK, so I have a Microsoft SQL Server 2014 database table called owner with around 90,000 records with owner information, another called vehicle with vehicle information

Owner_Name                   owner_id       V_name     owner_id    exempt
-------------------------------------       ------------------------------
JACOB JAMISON & JESSICA           35        Civic            35        H3
JACOB JAMISON M & JESSICA B       39        Accord           39        H3 
BLACKSON BARRINGTON               56        Bugatti          56        H6
BLACKSON BARRINGTON H             98        SSC              98        H7
BRUSTER MICHAEL                   107       Corvette         107       H9

I'm trying to find all records that have more than one exemption on a vehicle ( H0 means no exemption). This code below has worked well, as long as the name is exactly the same. However, if there's a variation, such as an extra letter or if it's entered backwards, then it won't return those records. I've looked at things like SOUNDEX, but this wouldn't work in my scenario.

SELECT Owner_name
     , COUNT(Owner_name) AS 'xNameAppears'
     , COUNT(v.exempt) AS 'ExemptionCount' 
FROM owner o
INNER JOIN vehicle V ON V.owner_id = o.owner_id
WHERE v.exempt <> 'H0'
GROUP BY O.owner_name
HAVING COUNT(v.exempt) > 1

Is there a solution that would allow me to return records like so, not knowing which owner_name's may be similar? Basically trying to get the server to search through the owner_name column and if there's a similarity such as JACOB JAMISON & JESSICA and JACOB JAMISON M & JESSICA B then it'll return those records like so:

Owner_Name                      xNameAppears      ExemptCount
-------------------------------------------------------------      
JACOB JAMISON & JESSICA           2                         2
JACOB JAMISON M & JESSICA B       2                         2
BLACKSON BARRINGTON               2                         2
BLACKSON BARRINGTON H             2                         2

Thank you in advance!

  • 1
    Sure SOUNDEX is suitable? The following all return the same value SOUNDEX('JACOB JAMISON & JESSICA '), SOUNDEX('Jacob Zuma'), SOUNDEX('Jacob Willekens'), SOUNDEX('Jacob S. Coxey, Sr.'), SOUNDEX('Juegief zzzzzzzzzzzz') – Martin Smith Nov 21 at 17:22
up vote 5 down vote accepted

The SOUNDEX function can be applied to a column as well.

But since

there's thousands like that

I wouldn't suggest just writing a query to join on a function to do that.

This will likely not perform very well on larger tables:

SELECT *
FROM dbo.vehicle AS v
JOIN dbo.vehicle AS v2
ON SOUNDEX(v2.Owner_Name) = SOUNDEX(v.Owner_Name)
AND v2.Owner_Name <> v.Owner_Name;

I'd rather do something that will make finding this easier in the long-term.

Here's an example:

CREATE TABLE dbo.vehicle (Owner_Name VARCHAR(50));
INSERT dbo.vehicle ( Owner_Name )
SELECT *
FROM (  
VALUES            
('JACOB JAMISON & JESSICA'),
('JACOB JAMISON M & JESSICA B'),
('BLACKSON BARRINGTON'),          
('BLACKSON BARRINGTON H'),        
('BRUSTER MICHAEL')
) AS x (Owner_Name);

I'm going to add a computed column based on the function, and then add an index to aid my query.

ALTER TABLE dbo.vehicle ADD Owner_Soundex AS SOUNDEX(Owner_Name);

CREATE INDEX ix_whatever ON dbo.vehicle (Owner_Soundex, Owner_Name);

Validate that everything looks good...

SELECT *
FROM dbo.vehicle AS v

Use a query like this to find imprecise matches:

SELECT *
FROM dbo.vehicle AS v
JOIN dbo.vehicle AS v2
ON v2.Owner_Soundex = v.Owner_Soundex
AND v2.Owner_Name <> v.Owner_Name;
  • You sir, are the man. I didn't think to use SOUNDEX in that manner. This works very well! Indeed that second way is more efficient performance wise. Thank you. – MindXpert Nov 21 at 16:12
  • @user1227080 happy to help! – sp_BlitzErik Nov 21 at 16:13

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.