SQL Where String Contains Using SQL Server
In SQL Server, a query can be performed using an exact match of a string via the != and/or = operators. But what if you want to match only a portion of a string or some other pattern? That’s where the LIKE operator comes in.
The LIKE operator is a case-insensitive pattern matching operator in SQL that allows for wildcards in the match. The associated wildcards are:
- [ ] – Any character in a range (e.g., [0-9]) or set (e.g., [abc]). This wildcard can additionally be negated with “^”, which means that it will only match if the characters are not in the range or set (e.g., [^abc]).
- _ – Underscore will match on any single character. Multiple underscores can be chained together as well. For example, __ will match on any two characters or ___ on any three characters.
- % – 0 or more of any character.
With some knowledge on what the LIKE operator is and the wildcards that are available, let’s dive into an example. The full example will first be shown followed by a breakdown of the individual piecs.
/* Create the tables and add rows */ CREATE TABLE TopSearch ( Id INT IDENTITY(1,1), Term VARCHAR(100), CONSTRAINT PK_SearchTest PRIMARY KEY (Id) ); INSERT INTO TopSearch(Term) VALUES ('Activities in April'), ('List of March sports'), ('April holidays'), ('December snow accumulation in Boise Idaho'), ('Top 10 April recipes'), ('Trends to follow in March and April'), ('Top 15 steps to marketing property'), ('Almost 90% of homeowners are forgetting this'), ('There has been a nearly 900% increase in this career'); /* Queries */ SELECT Term FROM TopSearch WHERE Term LIKE '%March%'; SELECT Term FROM TopSearch WHERE Term LIKE '%March sport_'; SELECT Term FROM TopSearch WHERE Term LIKE '%April%' AND Term NOT LIKE '%March%'; SELECT Term FROM TopSearch WHERE Term LIKE 'Top [0-9][0-9] %'; SELECT Term FROM TopSearch WHERE Term LIKE '%90!%%' ESCAPE '!'; /* Cleanup */ DROP TABLE IF EXISTS TopSearch;
Create the test table and add rows
In the first of the script, a table is being created to hold top search terms. Rows are then added to this table. There isn’t much to mention here other than that IDENTITY(1,1) is being used to create an auto-incrementing Id so that it won't have to manually be specified per row.
Search for “March” anywhere in Term
SELECT Term FROM TopSearch WHERE Term LIKE '%March%';
This query is looking for “March” with any character(s) before or after “March” via the % wildcard. The terms retrieved with this query are:
- List of March sports
- Trends to follow in March and April
Search for “March sport” with any character after sport
SELECT Term FROM TopSearch WHERE Term LIKE '%March sport_';
This query will retrieve any term that includes “March sport” at the end of the term where “sport” must have one character after it. In this case, only one term is retrieved: “List of March sports”.
Search for all terms containing “April” but not “March”
SELECT Term FROM TopSearch WHERE Term LIKE '%April%' AND Term NOT LIKE '%March%';
This query will retrieve any term that includes “April” but does not include “March”. The % wildcard is used for both the “April” and “March” pattern match but notice that NOT is included for the “March” pattern. NOT LIKE will exclude rather than include all matching items. The matching terms are
- Activities in April
- April holidays
- Top 10 April recipes
Search for “Top” with two numbers and a space
SELECT Term FROM TopSearch WHERE Term LIKE 'Top [0-9][0-9] %';
This query will retrieve any term that includes “Top” at the beginning followed by a space and two numbers in the 0-9 range using the  wildcard followed by another space and any characters after that via the % wildcard. Two terms are retrieved:
- Top 10 April recipes
- Top 15 steps to marketing property
Search using a wildcard with ESCAPE
SELECT Term FROM TopSearch WHERE Term LIKE '%90!%%' ESCAPE '!';
In some cases, you may need to match on a pattern where one or more of the wildcards needs to be used as part of the string rather than as a wildcard. In this example, we’re doing just that by searching for 90% anywhere in the string.
To allow this, we can use an escape character so that % won’t be treated as a wildcard. The escape character being used in this example is !. So, !% will match up to % in the string. The matching Term is “Almost 90% of homeowners are forgetting this”.
DROP TABLE IF EXISTS TopSearch;
Lastly, we are cleaning up the TopSearch table by dropping it. There isn’t anything too interesting to mention here.