0

I have a database containing only "name" column. it has entries like:

boyleston112, boylstonLLC, boylstnLLC
Amendment12, Amend12, Amendmnt

Also, I have distinct entries in the same table. I want such look alike entries to get listed. what query should I write? I have used self join but still there are many entries that are not covered.. the code follows:

SELECT *
FROM #QA_filter QA1
INNER JOIN #QA_filter QA2 ON QA1.name like QA2.name + '%' and QA1.Name<> QA2.Name

please help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3931799
  • 11
  • 5
  • 1
    This is a little confusing, maybe rephrasing the question could help. Do you want to pass in an entry and get back similar? Or do you want to just return result sets of similar entries? – vesuvious Aug 12 '14 at 04:27
  • 1
    What SQL technology are you working with? SQL Server? MySQL? – Jeff Aug 12 '14 at 04:30
  • i want result sets of similar entries . i have more than 14000 of entries from which i want only these look alike entries to get listed. and i m using SQL server – user3931799 Aug 12 '14 at 04:49

2 Answers2

1

May be like this?

SELECT group_concat(actual),left(snd,3) 
FROM 
(
select soundex('boyleston112') as 'snd','boyleston112' as 'actual'
UNION
select soundex('boylstonLLC') as 'snd','boylstonLLC' as 'actual'
UNION
select soundex('boylstnLLC') as 'snd','boylstnLLC' as 'actual'
UNION
select soundex('Amendment12') as 'snd','Amendment12' as 'actual'
UNION
select soundex('Amend12') as 'snd','Amend12' as 'actual'
UNION
select soundex('Amendmnt') as 'snd','Amendmnt' as 'actual'
)tmp
group by left(snd,3)
sumit
  • 15,003
  • 12
  • 69
  • 110
0

Try either soundex or levenshtein distance:

Implementation of Levenshtein distance for mysql/fuzzy search?

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex

Community
  • 1
  • 1
Rachael
  • 424
  • 2
  • 7