3

Does SQL Server have an equivalent function to Postgres Similar to compare strings?

I looking for something that will match 

str1 = "Flat 1, 110 Bob Street" 
str2 = "110, Bob St. Flat 1"

so I can do

select
...
from
t1 join 
t2  on (similar(t1.str1,t2.str2) >= 0.9)
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
jlondal
  • 43
  • 4
  • 1
    AFAIK, there's no way to do this within the db server. Normally you do this kind of thing on the client. Is it really necessary for you to be able to select matching addresses like this? See also http://stackoverflow.com/questions/2097323/what-are-ways-to-match-street-addresses-in-sql-server and http://stackoverflow.com/questions/6003591/mysql-street-address-fuzzy-search – tenfour Jan 13 '14 at 10:47
  • What edition of SQL Server are you using? If you have SQL Server Enterprise, you should consider using the Fuzzy Grouping functionality of SSIS: http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/ – Dan Jan 13 '14 at 12:08
  • 1
    @tenfour It only really gets handy to have this sort of thing in the DB server when you're then building expression indexes on them, or using them in materialized views, etc. – Craig Ringer Jan 13 '14 at 12:22
  • What problem do you try to solve? MSSQL has fuzzy search of strings using FREETEXT (http://technet.microsoft.com/ru-ru/library/ms176078.aspx) for example – Backs Jan 18 '14 at 07:31

1 Answers1

1

Here are some implementations of string distance functions in TSQL that might help:

Levenshtein

Jaro-Winkler (Registration required)

SoundEx

MatchText

I have had a lot of luck with Jaro-Winkler, but of course that's due to the nature of my data. The one that closest resembles the similarity query that you asked about is MatchText.

Community
  • 1
  • 1
Kirk Roybal
  • 17,273
  • 1
  • 29
  • 38