Network Programming in .NET

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)