String compare Stored procedure to return closest match

Experts..

I have the following stored procedure where I have data coming from @promocode I want to find the closest match to the string supplied. Below is what I have so far which matches exactly...I want the match to work finding the closest string or the first string that matches even if its only 1 character that matches.

CREATE PROCEDURE FindCorrectPromoCode
@PromoCode varchar(25)
AS
SELECT PromoCode FROM BrandseedPromoCodes
WHERE PromoCode = @PromoCode
DalexanAsked:
Who is Participating?
 
jdlambert1Connect With a Mentor Commented:
I suspect VB will handle it much more efficiently, but here's how it would look in T-SQL:

--you want the most precise possible matches at the top, getting less precise as you go down.

CREATE PROCEDURE FindCorrectPromoCode @PromoCodeIn varchar(25) AS

DECLARE @PromoCodeOut varchar(25)

SELECT @PromoCodeOut = PromoCode FROM BrandseedPromoCodes WHERE PromoCode = @PromoCodeIn

IF @PromoCodeOut IS NULL
  SELECT @PromoCodeOut = PromoCode FROM BrandseedPromoCodes
  WHERE PromoCode LIKE '%' + @PromoCodeIn + '%'

--series to remove one character at a time
IF @PromoCodeOut IS NULL
  SELECT @PromoCodeOut = PromoCode FROM BrandseedPromoCodes
  WHERE PromoCode LIKE '%' + LEFT(@PromoCodeIn,1) + '%' + SubString(@PromoCodeIn,3,50) + '%'

IF @PromoCodeOut IS NULL
  SELECT @PromoCodeOut = PromoCode FROM BrandseedPromoCodes
  WHERE PromoCode LIKE '%' + LEFT(@PromoCodeIn,2) + '%' + SubString(@PromoCodeIn,4,50) + '%'

--...

--series to remove two characters at a time
IF @PromoCodeOut IS NULL
  SELECT @PromoCodeOut = PromoCode FROM BrandseedPromoCodes
  WHERE PromoCode LIKE '%' + LEFT(@PromoCodeIn,1) + '%' + SubString(@PromoCodeIn,4,50) + '%'

IF @PromoCodeOut IS NULL
  SELECT @PromoCodeOut = PromoCode FROM BrandseedPromoCodes
  WHERE PromoCode LIKE '%' + LEFT(@PromoCodeIn,2) + '%' + SubString(@PromoCodeIn,5,50) + '%'

-- more series to remove more and more characters

--give error if no hits:
IF @PromoCodeOut IS NULL
 SET @PromoCodeOut = 'Promo Code Not Found'

SELECT @PromoCodeOut
0
 
arbertCommented:
CREATE PROCEDURE FindCorrectPromoCode
@PromoCode varchar(25)
AS
SELECT PromoCode FROM BrandseedPromoCodes
WHERE PromoCode like '%' +  @PromoCode + '%'
0
 
jdlambert1Commented:
The only fuzzy search functions I know of in SQL Server are CONTAINS and FREETEXT, but neither one matches on a per number of characters basis.

You could use a series of SELECT's that use LIKE and the % wildcard, with the first select using all the search characters, then dropping one character at a time until it gets a hit. You might also need to move the % around, like '%firstpart%', '%f%rstpart%', '%fi%stpart%', etc. This could be one looong series...

Anyone know a better way?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
arbertCommented:
The above can be pretty slow though....You could also use the Soundex function to look for misspellings....

Brett
0
 
arbertCommented:
"I know of in SQL Server are CONTAINS and FREETEXT,"   these also only work against full text indexes....

If you want one character matches--you can change your parm to only pass the one character....you can also use Left, Substring, Right within the proc to slice and dice your parm any way you like....


0
 
jdlambert1Commented:
Soundex is a great idea for names, but it's matching capability is very poor for word other than names. :(
0
 
DalexanAuthor Commented:
Yes, Soundex would have been good but this is alphanumeric codes...

Example would be

UT00032   may be entered as    UB00032
0
 
arbertCommented:
"but it's matching capability is very poor for word other than names. "  Not when used in conjunction with other functions--unless you're searching for words that are made up from all vowels or something....
0
 
DalexanAuthor Commented:
jdlambert1's answer seems what I will have to do. Can someone put this into my stored proc so I can get an idea of the method?
0
 
jdlambert1Commented:
Which other functions for Soundex? I had to benchmark Soundex accuracy in a lab, got unacceptably poor matching on everything except names, then learned the algorithm was designed specifically for matching names. There are other algorithms that do a far better job for non-name text, but Soundex is the only one built into SQL Server...
0
 
Scott PletcherSenior DBACommented:
You might also want to create your own custom scalar-function that returned a "match" value based on whatever criteria you wanted.
0
 
DalexanAuthor Commented:
I think I am going to do the pattern matching within my VB function rather than the stored procedure since there is no real good way to do it within the stored proc.
0
 
Ken SelviaRetiredCommented:
Will this work?

-- Create a table of numbers if you don't have one
SELECT IDENTITY(INT) AS Number INTO Numbers FROM sysobjects s1 CROSS JOIN sysobjects s2

--
Create Function f_expand (@str varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT ID  Rank, substring(@str,1,ID) Parts
      FROM Numbers
      WHERE ID <= len(@str))

-- To see what it does:
-- Select * FROM dbo.f_expand('Code1')

--Test data
Create Table BrandseedPromoCodes (PromoCode Varchar(20))
insert BrandseedPromoCodes Select 'Code1'
insert BrandseedPromoCodes Select 'Code1 And 2'
insert BrandseedPromoCodes Select 'Code2 And 3'
insert BrandseedPromoCodes Select 'Code4'
insert BrandseedPromoCodes Select 'Code5 And 2'
insert BrandseedPromoCodes Select 'Cord'

--Search for promocodes matching any part of the input code.
select promocode,  Rank , convert(varchar(10),s.parts) parts
from BrandseedPromoCodes , dbo.f_expand('Code1') s
where PromoCode like s.Parts + '%'
order by rank desc

promocode            Rank        parts      
-------------------- ----------- ----------
Code1                5           Code1
Code1 And 2          5           Code1
Code1                4           Code
Code1 And 2          4           Code
Code2 And 3          4           Code
Code4                4           Code
Code5 And 2          4           Code
Code1                3           Cod
Code1 And 2          3           Cod
Code2 And 3          3           Cod
Code4                3           Cod
Code5 And 2          3           Cod
Code1                2           Co
Code1 And 2          2           Co
Code2 And 3          2           Co
Code4                2           Co
Code5 And 2          2           Co
Cord                 2           Co
Code1                1           C
Code1 And 2          1           C
Code2 And 3          1           C
Code4                1           C
Code5 And 2          1           C
Cord                 1           C


-- To select top 10 percent or whatever...

select top 10 percent promocode,  Rank
from BrandseedPromoCodes , dbo.f_expand('Code1') s
where PromoCode like s.Parts + '%'
order by rank desc

promocode            Rank        
-------------------- -----------
Code1                5
Code1 And 2          5
Code1 And 2          4
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.