Solved

String compare Stored procedure to return closest match

Posted on 2004-08-04
13
1,439 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Dalexan
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11719593
CREATE PROCEDURE FindCorrectPromoCode
@PromoCode varchar(25)
AS
SELECT PromoCode FROM BrandseedPromoCodes
WHERE PromoCode like '%' +  @PromoCode + '%'
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11719600
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
 
LVL 34

Expert Comment

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

Brett
0
 
LVL 34

Expert Comment

by:arbert
ID: 11719642
"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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11719658
Soundex is a great idea for names, but it's matching capability is very poor for word other than names. :(
0
 

Author Comment

by:Dalexan
ID: 11719822
Yes, Soundex would have been good but this is alphanumeric codes...

Example would be

UT00032   may be entered as    UB00032
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:arbert
ID: 11719830
"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
 

Author Comment

by:Dalexan
ID: 11719854
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11719894
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 11719970
You might also want to create your own custom scalar-function that returned a "match" value based on whatever criteria you wanted.
0
 

Author Comment

by:Dalexan
ID: 11720055
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
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 125 total points
ID: 11720085
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
 
LVL 12

Expert Comment

by:kselvia
ID: 11723347
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now