Skip to content

Exact String Searching without a Full-Text index

October 16, 2014

An annoying case when using standard LIKE wildcard searching of text columns is when you only want the exact word searched for e.g. “Search” and not “Searchable” or “Research

So, you add a space to sides of the searchterm:
WHERE MyDescription LIKE ‘% ‘ + @searchterm + ‘ %’

but, then you realise that this misses cases when the search term appears at the start or end of the column you are searching, so you try:
WHERE ‘ ‘ + MyDescription + ‘ ‘ LIKE ‘% ‘ + @SearchTerm + ‘ %’

usually, this would be a killer to any use of an index on the MyDescription column – but as we are searching for a string anywhere in the column then a non-FULLTEXT index wont help us anyway

Then you realise that you are missing cases when the search term is adjacent to a fullstop or some html
e.g.: “When I look, I search.” or “Why don’t I just use Solr for this search<somehtmltag>

So, I like to use this (for non-case, non-accent sensitive collations)
WHERE ‘ ‘ + MyDescription + ‘ ‘ LIKE ‘%[^a-z]’ + @SearchTerm + ‘[^a-z]%’

this excludes any occurrences of the search term within another word, but allows:

hyphens  e.g. re-search

punctuation e.g. search,  search. Search!

Numeric compounds e.g. Search101

Html tags e.g. search<somehtmltag>

However, this wont work for unicode foreign languages e.g. Rзsearch

need some way to translate [^a-z] to Russian.

WHERE N’ ‘ +mydescription + N’ ‘ LIKE N’%[^a-Я]’ + @SearchTerm + N'[^a-Я]%’

you just need to make sure you get the a from the russian alphabet – use the onboard keyboard with Russian language on windows

seems to work but needs some more testing… I cant decide how the LIKE interprets the range – presumably on Unicode value, but how does it do the accent sensitivity?

declare @SearchTerm NVARCHAR(50) = ‘search’
declare @t table(mydescription NVARCHAR(50))

insert into @t(mydescription) values(‘Search me’),(‘Research me’), (N’Rзsearch me’), (‘Résearch me’), (N’%search me’), (N’.search me’), (N'</br>search me’)
, (N’RΨsearch me’)

SELECT * FROM @t
WHERE N’ ‘ +mydescription + N’ ‘ LIKE N’%[^a-z]’ + @SearchTerm + N'[^a-z]%’

SELECT * FROM @t
WHERE N’ ‘ +mydescription + N’ ‘ LIKE N’%[^a-z^А-Я]’ + @SearchTerm + N'[^a-z^А-Я]%’

SELECT * FROM @t
WHERE N’ ‘ +mydescription + N’ ‘ LIKE N’%[^a-Я]’ + @SearchTerm + N'[^a-Я]%’

Advertisements

From → Uncategorized

Leave a Comment

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: