?
Solved

String compare Stored procedure to return closest match

Posted on 2004-08-04
13
Medium Priority
?
1,452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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
 
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:Scott Pletcher
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 375 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.
Suggested Courses

777 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