Solved

Fuzzy logic in SQL

Posted on 2013-01-21
8
2,490 Views
Last Modified: 2016-02-11
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
Comment
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
  • Learn & ask questions
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
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

by:tliotta
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

by:RehanYousaf
ID: 38805128
Please provide with sample data and logic you want to use
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:sqlcurious
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.
Please let me know if you need more info, thanks
sample-data.txt
0
 

Author Comment

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

Accepted Solution

by:
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

by:sqlcurious
ID: 38807527
thanks!
0
 
LVL 5

Expert Comment

by:RehanYousaf
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

Open in new window


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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

734 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