Searching with LIKE

Having a clue on how your search function performs its search could come in very handy. For instance, our search application makes use of SQL’s LIKE clause in the where condition. So if I were to enter a search term “hello world”, the search in the database would be something like:

select * from TABLETOSEARCH 
where searchableField like '%hello world%';

There are certain characters that work differently with LIKE. So knowing these characters could be helpful in exposing bugs that might cause the search function to behave differently from what’s expected. The table below illustrates some examples but the behavior could possible vary depending on the database being used.

Character

LIKE behavior of the character

Search term

Expected

Actual Result

‘ (apostrophe)

I don’t like patatas

Return Paul

BUG -“Sorry, an error has occurred.”

% (percent)

Allows you to match any string of any length (including zero length)

pam%ela

Shouldn’t return Pamela Lesley since her data doesn’t actually contain “pam%ela”

BUG – returned Pam

_ (underscore)

Allows you to match on a single character

pam_la le_le_

Shouldn’t return Pamela Lesley

BUG – returned Pam

[ ] (with brackets)

Allows you to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters)

[pb]amela

Shouldn’t return Pamela Lesley

BUG – returned Pam

[^] (with caret in brackets)

Allows you to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters)

[^b]amela

Shouldn’t return Pamela Lesley

BUG – returned Pam

READ MORE: