Strings Matching - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Strings Matching

Author

Topic

linda9898
Starting Member

28 Posts

Posted - 2009-02-15 : 08:07:44

Hi ,

Can anyone please help me to find a procedure which find matching between strings by percentages ?
i remember that i had it once but i deleted it.

for example :
1st string - aaab
2nd string - aaa

so it will approx calculate it to be 75% match

many thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-15 : 13:42:47

[code]CREATE FUNCTION dbo.fnStringCompare
(
@String1 VARCHAR(20),
@String2 VARCHAR(20)
)
RETURNS DECIMAL(5, 2)
AS
BEGIN
RETURN (
SELECT 1.0E *
SUM(CASE
WHEN COALESCE(s1.i, 0) < COALESCE(s2.i, 0) THEN s1.i
ELSE COALESCE(s2.i, 0)
END) /
CASE
WHEN SUM(COALESCE(s1.i, 0)) > SUM(COALESCE(s2.i, 0)) THEN SUM(COALESCE(s1.i, 0))
ELSE SUM(COALESCE(s2.i, 0))
END
FROM (
SELECT SUBSTRING(@String1, Number + 1, 1) AS c,
COUNT(*) AS i
FROM master..spt_values
WHERE Type = 'P'
AND Number < CASE
WHEN @String1 IS NULL OR @String1 = '' THEN 0
ELSE DATALENGTH(@String1) / DATALENGTH(SUBSTRING(@String1, 1, 1)) -- Unicode aware
END
GROUP BY SUBSTRING(@String1, Number + 1, 1)
) AS s1
FULL JOIN (
SELECT SUBSTRING(@String2, Number + 1, 1) AS c,
COUNT(*) AS i
FROM master..spt_values
WHERE Type = 'P'
AND Number < CASE
WHEN @String2 IS NULL OR @String2 = '' THEN 0
ELSE DATALENGTH(@String2) / DATALENGTH(SUBSTRING(@String2, 1, 1)) -- Unicode aware
END
GROUP BY SUBSTRING(@String2, Number + 1, 1)
) AS s2 ON s2.c = s1.c
)
END[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-15 : 14:28:01

What about "abcde" and "edcba"?
Are they 20% or 100% match?

E 12°55'05.63"
N 56°04'39.26"

linda9898
Starting Member

28 Posts

Posted - 2009-02-16 : 02:58:47

Hi peso ,

its great , thanks for your help

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-02-16 : 12:40:02

Also, try this for fuzzy-search matching:
http://sqlblindman.pastebin.com/m7934cf9e

blindman

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources