Wildcard Basics in SQL Server: How LIKE Patterns Work

Wildcard basics start with the LIKE operator, which matches text against a pattern instead of an exact value. A pattern can hold special symbols, and each symbol stands for something different. Four of them cover most of the patterns you’ll write.

The Sample Table

Every example uses one small table of last names. Think of the check-in desks at a conference. Desk 1 takes last names A to K. Desk 2 takes L to Q. Desk 3 takes R to Z. Wildcards can build those three lists. Run this in any test database. The Coupons table is for the last section, and every result comes from SQL Server 2025.

CREATE TABLE Attendees (AttendeeID INT IDENTITY(1,1) PRIMARY KEY, LastName VARCHAR(30) NOT NULL);
INSERT INTO Attendees (LastName) VALUES
  ('Adams'), ('Baker'), ('Brooks'), ('Clark'), ('Kumar'), ('Lopez'), ('Moreno'),
  ('Nguyen'), ('Patel'), ('Quinn'), ('Rossi'), ('Singh'), ('Taylor'), ('Zhang');
GO

CREATE TABLE Coupons (Code VARCHAR(20) NOT NULL);
INSERT INTO Coupons (Code) VALUES ('SAVE10%'), ('SAVE_20'), ('SAVE30'), ('SAVE-40'), ('SAVE50');
GO

The Four Wildcards

• % matches any run of characters, including none.
• _ matches exactly one character.
• [ ] matches one character from a set or range, such as [ABC] or [A-K].
• [^ ] matches one character that is not in the set or range.

Wildcards work only with LIKE. Start with the easy one, names that begin with B.

SELECT LastName FROM Attendees WHERE LastName LIKE 'B%';

Where you put the % decides the meaning. ‘T%’ begins with T, ‘%s’ ends with s, and ‘%o%’ has an o anywhere in the name.

SELECT LastName FROM Attendees WHERE LastName LIKE 'T%';

SELECT LastName FROM Attendees WHERE LastName LIKE '%s';

SELECT LastName FROM Attendees WHERE LastName LIKE '%o%';

Sets and Ranges

Names that start with A or B take two predicates joined by OR. Desk 1 needs A to K, and an OR chain for that takes eleven predicates.

SELECT LastName FROM Attendees
WHERE LastName LIKE 'A%'
   OR LastName LIKE 'B%'
   OR LastName LIKE 'C%'
   OR LastName LIKE 'D%'
   OR LastName LIKE 'E%'
   OR LastName LIKE 'F%'
   OR LastName LIKE 'G%'
   OR LastName LIKE 'H%'
   OR LastName LIKE 'I%'
   OR LastName LIKE 'J%'
   OR LastName LIKE 'K%';

A set in square brackets does the same job in one predicate. List the letters you want for the first position. The order of the letters doesn’t matter, so both queries below return the same five names.

SELECT LastName FROM Attendees WHERE LastName LIKE '[ABCDEFGHIJK]%';

SELECT LastName FROM Attendees WHERE LastName LIKE '[KBCDEFGHIJA]%';

A range is shorter still. Write the first and last letter with a hyphen between them.

SELECT LastName FROM Attendees WHERE LastName LIKE '[A-K]%';

Don’t confuse the two. [AK] is a set of two letters, not a range. It returns only Adams and Kumar.

SELECT LastName FROM Attendees WHERE LastName LIKE '[AK]%';

Mistakes With = and <=

An equal sign compares the whole value. It doesn’t read a pattern, so it treats [A-K]% as plain text and returns no rows.

SELECT LastName FROM Attendees WHERE LastName = '[A-K]%';

A less-than comparison looks closer, but it fails in another way. It compares text by sort order and treats % as an ordinary character. Kumar sorts after ‘K%’, so it drops out. Only LIKE gives you pattern matching.

SELECT LastName FROM Attendees WHERE LastName <= 'K%';

The Three Desks

Now the three registration lists are easy to build, one range per desk.

SELECT LastName FROM Attendees WHERE LastName LIKE '[A-K]%';

SELECT LastName FROM Attendees WHERE LastName LIKE '[L-Q]%';

SELECT LastName FROM Attendees WHERE LastName LIKE '[R-Z]%';

Underscore and Not

An underscore stands for exactly one character, so ‘_a%’ finds names with an a in the second position. A caret inside the brackets reverses the set. ‘[^A-K]%’ returns every name that doesn’t start with A to K.

SELECT LastName FROM Attendees WHERE LastName LIKE '_a%';

SELECT LastName FROM Attendees WHERE LastName LIKE '[^A-K]%';

NOT LIKE reverses a whole pattern. It returns the same nine names as ‘[^A-K]%’.

SELECT LastName FROM Attendees WHERE LastName NOT LIKE '[A-K]%';

Case and Accents

On a case-insensitive collation, ‘[a-k]%’ and ‘[A-K]%’ return the same rows. A binary collation compares exact characters, so only the capital letters match this table.

SELECT LastName FROM Attendees WHERE LastName LIKE '[a-k]%';

SELECT LastName FROM Attendees WHERE LastName COLLATE Latin1_General_BIN LIKE '[a-k]%';

SELECT LastName FROM Attendees WHERE LastName COLLATE Latin1_General_BIN LIKE '[A-K]%';

Ranges follow the collation, not the plain alphabet. The text below holds two accented letters. In a database with the collation SQL_Latin1_General_CP1_CI_AS, ‘%[^a-z]%’ finds nothing unusual in it. Both letters count as inside a to z. A binary collation flags them.

DECLARE @Name NVARCHAR(40) = N'arnetonsberghosøvarnanoü';
SELECT
  CASE WHEN @Name LIKE '%[^a-z]%' THEN 'special characters found' ELSE 'letters only' END AS DefaultCollation,
  CASE WHEN @Name COLLATE Latin1_General_BIN LIKE '%[^a-z]%' THEN 'special characters found' ELSE 'letters only' END AS BinaryCollation;

Matching a Wildcard Literally

The Coupons table has codes with a real underscore and a real percent sign. The first query returns all five codes, because the underscore matches any character. [_] matches only an underscore, and [%] only a percent sign. ESCAPE does the same by marking the next character as literal.

SELECT Code FROM Coupons WHERE Code LIKE 'SAVE_%';

SELECT Code FROM Coupons WHERE Code LIKE 'SAVE[_]%';

SELECT Code FROM Coupons WHERE Code LIKE '%[%]';

SELECT Code FROM Coupons WHERE Code LIKE '%!%' ESCAPE '!';

Clean Up

Remove the two sample tables when you finish.

DROP TABLE Coupons;
DROP TABLE Attendees;

What to Remember

Use LIKE, not the equal sign, for patterns. Use a set for a few letters and a range for a run of letters, and don’t mix them up. Test a new pattern on a small table first, as you did here.

To go further, read LIKE Character Ranges: [a-f], [^0-9] and Collation Traps, Escaping Wildcards in LIKE and Leading Wildcard LIKE Searches.

A wildcard is not a shortcut for typing, it is a rule for which characters can match.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.