SQL Like — Wildcards Empower Searching

Relational databases store information in tables — tables with columns analogous to elements in a data structure and rows which are an instance of that data structure. Sometimes the question becomes how to identify the specific data needed, how to filter away the unwanted. The SQL-language SELECT statement extracts data from those tables, its WHERE clause filters out data which doesn’t satisfy specified conditions, and the LIKE modifier filters existing data through exact matches and wildcard searches. This blog entry will demonstrate use of the LIKE clause and cover wildcard characters and their use in search patterns.

The general syntax of SQL Like is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name
LIKE pattern ;

The pattern is a matching filter for column values; an alphanumeric string of characters which consist of special “wildcard” characters (listed below) and regular characters (which are an exact match).

Exact matches can be accomplished with two equivalent searches, but LIKE gives us room to grow our searches:

SELECT names FROM friends WHERE firstname = 'Moriarty' ;
SELECT names FROM friends WHERE firstname LIKE 'Moriarty' ;

The first choice, an equality test, is easy to understand. It’s the second, LIKE, which gives a hint as to the power available to us.

SQL Like Wildcard Symbols — The Common Foundation

The most-used wildcard symbols are:

•  The percent sign — % — match zero, one, or multiple characters (also known as “match all”)
•  The underscore character — _ — match a single character (also known as “match one”)

First we create a database table to hold sample words for the following SQL Like examples:

CREATE TABLE examples (
wordsVARCHAR(20)
);
INSERT INTO examples ( words )
VALUES
( "overcontrolling" ), ( "overdiscounting" ), ( "overdocumenting" ), ( "overencouraging" ), ( "overengineering" ), ( "overspeculating" ), ( "overstimulating" ), ( "oversubscribing" ), ( "overwithholding" ) ;

Matches Which Begin With A Known String

Return all words which begin with the string “over”:

SELECT * FROM examples WHERE words LIKE 'over%' ;

Matches Which End With A Known String

Return all words which end with the string “ing”.

SELECT * FROM examples WHERE words LIKE '%ing' ;

Matches Which Contain A Known String

Return all words which contain the string “en” somewhere within.

SELECT * FROM examples WHERE words LIKE '%en%' ;

Matches Which Contain Known Strings By Position

Return all words which start with the string “over” and have the letter ‘n’ in the sixth position.

SELECT * FROM examples WHERE words LIKE 'over_n%' ;

Matches Of A Minimum Length

Return all words which begin with the letter ‘o’ and are at least two characters long. (Add additional underscores to increase the minimum length.

SELECT * FROM examples WHERE words LIKE 'o_%' ;

Matches Which Contain Known Strings At The Beginning and End

Return all words which begin with the letter ‘o’ and end with the letter ‘g’.

SELECT * FROM examples WHERE words LIKE 'o%g' ;

SQL Like Wildcard Symbols — Extended Functionality

Matches Which Contain Any Of A Set Of Known Strings

Return all words which begin with any of the letters ‘a’, ‘b’, or ‘c’ (those letters enclosed within the square brackets).

SELECT * FROM examples WHERE words LIKE '[abc]%' ;

Matches Which Do Not Contain Any Of A Set Of Known Strings

Return all words which do not begin with any of the letters ‘a’, ‘b’, or ‘c’ (those letters enclosed within the square brackets, negated with the exclamation mark).

SELECT * FROM examples WHERE words LIKE '[!abc]%' ;

Matches Which Contain A Letter Range

Return all words which begin with any of the letters between ‘l’ and ‘p’, inclusive (the same as [lmnop]).

SELECT * FROM examples WHERE words LIKE '[l-p]%' ;

Matches Which Contain Numbers

Return all words which start, end, or contain one or more numbers using the hash mark — # — to show the position of numbers.

SELECT * FROM examples WHERE words LIKE '#%' ; -- start with a number
SELECT * FROM examples WHERE words LIKE '%##' ; -- end with two numbers

Conclusion

Covered here are the search techniques and wildcard symbols used by SQL Like to match and filter results according to a customized pattern. The exact pattern syntax shown in the examples may need to be modified for different database implementations.

Further readings include a deeper Udacity SQL Language Tutorial a topic-by-topic list of Udacity Hub of SQL Language Topics.

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

SQL Sample Table Code

The following will build the code sample tables referred to in this blog post. Examine them interactively at  SQL Fiddle or CodingGround.

CREATE TABLE examples (
wordsVARCHAR(20)
);
INSERT INTO examples ( words )
VALUES
( "overcontrolling" ), ( "overdiscounting" ), ( "overdocumenting" ), ( "overencouraging" ), ( "overengineering" ), ( "overspeculating" ), ( "overstimulating" ), ( "oversubscribing" ), ( "overwithholding" ) ;