Home > Uncategorized > Getting a numeric value for the similarity of two text values in SQL Server

Getting a numeric value for the similarity of two text values in SQL Server

If you don’t have the luxury of a full text index on your free SQL server express database, or can’t set it up, because someone else manages or hosts, it, then a work around is this function that returns a numeric value for the similarity of two text values (which can be used as an order-by clause later)

create function func_similarity
(@TextBody varchar(8000),
@SearchTerm varchar(4000))
returns int
as
BEGIN
if ( @SearchTerm = @TextBody ) return 2
if ( @Textbody like '%' + @SearchTerm + '%') return 1
if ( @SearchTerm like '%' + @Textbody + '%') return 1
return 0
END

In my case, I was looking for a ‘kind of’ free text lookup on a table of US zip codes:

create proc FindUSPostcode
@city varchar(100)
as
select top 10 * from postcodeus
order by
dbo.func_similarity(Town,@city)
+ dbo.func_similarity(City,@city)
+dbo.func_similarity(state,@city)
desc

This meant that a lookup for

FindUSPostcode('Detroit')

Returns a top result for Detroit, Maine (Somerset county). But ‘Detroit, MI’ returns a top result for Detroit Michigan.

An API will come soon! (postcodefinder.org.uk)

Advertisements
Categories: Uncategorized
  1. December 15, 2011 at 7:26 pm

    Here’s the API for the US Zip code lookup:
    http://postcodefinder.org.uk/api/lookup.aspx?address=detroit

    returns data on JSON

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: