Solved

# Fuzzy logic in SQL

Posted on 2013-01-21
2,490 Views
WonkinlastyearnoGuitarLotsfloorlotsonly
WonkinlastyearnoGuitarLots
Hi experts, I need to calculte the match percentage of these two, is there a way to do it? I have heard about fuzzy logic but have no clue as to how to do it, please help
0
Question by:sqlcurious
[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

LVL 75

Expert Comment

ID: 38803545
Hi experts, I need to calculte the match percentage of these two, is there a way to do it?
You cannot do that with T-SQL, you will have to resort to writing a CLR function to accomplish that.
0

LVL 27

Expert Comment

ID: 38803832
What do those names reference? I.e., what is the meaning of the data and what is the data definition?

Tom
0

LVL 5

Expert Comment

ID: 38805128
Please provide with sample data and logic you want to use
0

Author Comment

ID: 38806386
Please look at the attached sample data, and I have the logic in place to figure out the exact matches but I  want the logic to see if there are any close matches in terms of percentage -
For ex: DescrCode 1 and 2 has exact match Description1 and Description2 , DescCode 3 - 6 match > 50% , 7 and 8 less than 50% , this is just tentative percentages but can be more definitive.
sample-data.txt
0

Author Comment

ID: 38806397
this is the correct sample file, pls ignore previous one, thanks
sample-data.txt
0

LVL 6

Accepted Solution

liija earned 500 total points
ID: 38806751
Could be done with SQL - but might become pretty complex code.
The easiest way is to use SQL Server Integration Services. There's a component that you might use on this, Fuzzy Lookup.

Here's a decent step-by-step guide - even if you have never used SSIS before:
http://oakdome.com/programming/SSIS_FuzzyLookup.php
0

Author Closing Comment

ID: 38807527
thanks!
0

LVL 5

Expert Comment

ID: 38807578
Well there are different ways of doing it e.g

1. As  acperkins said, you can use CLR function to accomplish and the example is
http://anastasiosyal.com/POST/2009/01/11/18.ASPX?

2. As liija said, use SSIS package to do the job

3. If you have SQL Server 2008 R2 then a another option is to install Master Data Services
http://msdn.microsoft.com/en-us/library/ee633742(v=sql.105).aspx

sample code will be something like
``````DECLARE
@Name VARCHAR(100),
@Name2 VARCHAR(100)

SET @Name = 'rehan yousaf'
SET @Name2 = 'rayhaan usuf'

SELECT
@Name AS [@Name],
@Name2 AS [@Name2],
MDS.mdq.Similarity(@Name, @Name2, 0, 0.85, 0) AS Levenshtein,
MDS.mdq.Similarity(@Name, @Name2, 1, 0.85, 0) AS Jaccard,
MDS.mdq.Similarity(@Name, @Name2, 2, 0.85, 0) AS JaroWinkler,
MDS.mdq.Similarity(@Name, @Name2, 3, 0.85, 0) AS LongestCommonSubsequence
``````

I have also attached the screenshot for option 3 as well
FuzzyLogic01.JPG
0

## Featured Post

Question has a verified solution.

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

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â€¦
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month5 days, 7 hours left to enroll