Notes

Search for characters in SQL with LIKE and CONTAIN

searching for substrings in sql with LIKE and its % and _ wildcards vs CONTAINS(), and why CONTAINS doesn’t work on plain strings in mysql

Edit on GitHub

Databases
2 minutes
SQL MySQL
  • You have two options: LIKE and CONTAINS(). CONTAINS() is supposed to be more performant
  • LIKE, if it starts with a wildcard, will require a full table scan
  • % and _ are wildcards for the LIKE operator. % represents zero, one or multiple characters. _ represents a single character (e.g. %ish will represent everything that ends in ish like Lavish, McTavish and so on)
  • * is a wildcard in CASE statements. e.g. *test* will find words like testing and greatest as well.
  • CONTAINS is not a standard SQL function. The implementation varies across servers and the required arguments vary as well.
  • In MySQL, CONTAINS does not work on ordinary strings (it was developed as an implementation of the OpenGIS framework and only works when dealing with spatial data, whatever that means).

LIKE operator

1SELECT * FROM table WHERE Column LIKE 'test'; -- test
2SELECT * FROM table WHERE Column LIKE 'test%'; -- test, testing, tested ..
3SELECT * FROM table WHERE Column LIKE '%test'; -- test, greatest, latest ..
4SELECT * FROM table WHERE Column LIKE '%test%'; -- test, greatest, latest, testing, tested ..
5
6SELECT * FROM table WHERE Column LIKE '_r%' -- Finds any values that have "r" in the second position e.g. Orphan
7SELECT * FROM table WHERE Column LIKE 'a_%_%'	-- Finds any values that starts with "a" and are at least 3 characters in length
8SELECT * FROM table WHERE Column LIKE 'a%o'	-- Finds any values that starts with "a" and ends with "o"

CONTAINS() function

1-- SELECT columnName FROM yourTable WHERE CONTAINS (columnName, ‘yourSubstring’);
2SELECT * FROM table WHERE CONTAINS(column, 'test'); -- test
3SELECT * FROM table WHERE CONTAINS(column, 'test*'); -- test, testing, tested ..
4SELECT * FROM table WHERE CONTAINS(column, '*test'); -- test, greatest, latest ..
5SELECT * FROM table WHERE CONTAINS(column, '*test*'); -- test, greatest, latest, testing, tested ..
6
7-- search for multiple substrings (AND, OR)
8SELECT DocID, DocSummary FROM production.documents WHERE CONTAINS (DocSummary, ‘”replacing” OR “pedals”’);

Searching for multiple words/substrings

If you need all words to be present, use OR

1SELECT * FROM table
2WHERE column LIKE '%word1%'
3   OR column LIKE '%word2%'
4   OR column LIKE '%word3%'

If you need all words to be present, use AND

1SELECT * FROM table
2WHERE column LIKE '%word1%'
3  AND column LIKE '%word2%'
4  AND column LIKE '%word3%'

Related

Browse all topics