Structured Query Language/Like Predicate

Since its first days SQL includes a basic form of pattern matching on strings. The feature is part of the WHERE clause and is triggered by the key word LIKE. It knows two meta-characters: '_' (underscore) and '%' (percent).

SELECT * 
FROM   <table_name>
WHERE  <column_name> LIKE <like_criterion>; -- The like_criterion can contain '_' and/or '%'

Over time, additional functionality has been incorporated to the standard. This concerns regular expressions, which are similar to those defined by POSIX, and regular expressions as defined by the XQuery 1.0 / XPath 2.0 function fn:matches(). Finally the SQL application package ISO/IEC 13249-2:2003 Part 2: Full-Text defines methods and functions for information retrieval on fulltext.

LIKE

The LIKE predicate compares a column of type CHAR or VARCHAR (string) with a pattern. This pattern is also a string, but it may contain two characters with a special meaning. The '_' (underscore) represents exactly one arbitrary character and '%' (percent) represents a string of zero, one or more characters. All other characters represent themselfes.

The first example retrieves all rows from table person with a first name starting with the two characters 'Jo', eg: 'John', 'Johanna' or 'Jonny W.'. As the meta-character '%' represents also zero characters, rows with the first name 'Jo' are also retrieved.

SELECT * 
FROM   person
WHERE  firstname LIKE 'Jo%';

The next example retrieves all rows from table person with a last name similar to 'Meier', eg: 'Meyer' or 'Maier'. The two underscores represents exactly two (arbitrary) characters. Consider, that there may be unexpected results like 'Miler' (but not 'Miller').

SELECT * 
FROM   person
WHERE  lastname LIKE 'M__er';

The definition of the two meta-characters '_' and '%' implies a problem: What if one wants to search for this characters itself? The solution is the same as in other programming languages: one defines an escape mechanism to mask the meta-characters and prefix the meta-character in the pattern by this escape character. In SQL the syntax is:

...
WHERE <column_name> LIKE <like_criterion> ESCAPE <escape_character>;

-- Retriev one of the meta-characters: 'Electric_Motor' but not 'Electric Motor'
SELECT * 
FROM   parts
WHERE  partname LIKE 'Electric\_Motor' ESCAPE '\';

-- Retrieve the escape character itself: 'Electric\Motor' but not 'Electric Motor'
SELECT * 
FROM   parts
WHERE  partname LIKE 'Electric\\Motor' ESCAPE '\';

The escape character can be any character, it is not limited to the back slash, eg.: ... ESCAPE '!'

POSIX Semantic

In an outdated version of the SQL standard there was the key word SIMILAR which introduced a pattern match in the sence of POSIX. Nowadays it is deprecated.

Some implementations offer pattern matching in this sense, but use arbitrary key words like REGEXP, REGEXP_MATCHES, REGEXP_LIKE or operators like '~' to activate the feature.

XQuery Semantic

The SQL standard defines the key word LIKE_REGEX to activate pattern matching as defined by the XQuery 1.0 / XPath 2.0 function fn:matches().

Full-Text

The SQL standard is complemented by ISO/IEC 13249 SQL Multimedia and Application Packages. Part 2 Full-Text defines routines and methods for information retrieval in text documents. It deals with objects like: word, phrase, sentence and paragraph. Therein are special constructs to seach for words or phrases, words within a definable distance, thesaurus features like broader terms of a word, soundex-similarity, ranking of results, and much more. The central methode of this features is CONTAINS.

-- Retrieve rows with 'word_1' and 'word_2' (in this order) within a sequence of 10 words.
SELECT * 
FROM   t1
WHERE  CONTAINS(' "word_1" NEAR "word_2" WITHIN 10 WORDS IN ORDER ') = 1;

Such features are fare beyond pattern matching - but it is the natural next step. SQL implementations offer them mostly in combination with special text indexes.