Monthly Archives: October 2014

Exact String Searching without a Full-Text index

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