The LIKE operator is used in the WHERE clause to search for a specific pattern in a column.

LIKE SQL statement

The LIKE operator is used to search for a specific pattern in a column.

SQL LIKE Syntax

Demo database

In this tutorial, we will be using the well-known Northwind database.

Below is a selection from the "Customers" table:

User ID	Client Name	The contact person	Address	city	Postcode	Country
1	Alfred Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados and helados	Ana Trujillo	Avda. de la Constitucion 2222	Mexico D.F.	05021	Mexico
3	Antonio Moreno Taqueria	Antonio Moreno	Mataderos 2312	Mexico D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbkop	Christina Berglund	Berguvsvagen 8	Lulea	S-958 22	Sweden

SQL LIKE Operator Examples

The following SQL statement selects all customers with a city starting with the letter "s" :

Advice: The "%" sign is used to define the mask (missing letters) before and after the drawing. You will learn more about wildcards in the next chapter.

The following SQL statement selects all clients with a city ending with the letter "s" :

The following SQL statement selects all clients with a country containing the pattern "land" :

Using the keyword prevents you from selecting entries that do not match the pattern.

The following SQL statement selects all clients with a country that does not contain the pattern "land" .

LIKE and REGEXP_LIKE

LIKE and REGEXP_LIKE operators in queries Oracle SQL, symbols % and _, regular expressions in requests

Very often in practical work it becomes necessary to conduct a character set search anywhere in a column - for example, to search for all records with product names containing a certain word, or to search for a word in both singular and plural. For this purpose, you can use the LIKE operator in Oracle SQL (it can also be used in SQL Server) and the REGEXP _LIKE operator (this operator is not provided in SQL Server).

Let's just say that LIKE and REGEXP _LIKE are used only in relatively simple situations. If you need to search based on grammar, noise word filtering, etc., then you should consider using full-text search.

The LIKE operator is used to match character values ​​against a pattern with special wildcard characters (metacharacters). Note that Oracle provides four variations of this operator:

· "normal" LIKE - used to query string columns with traditional encodings;

· LIKEC - used for columns with Unicode encoding (in Oracle terminology - Unicode complete );

· LIKE2 - for UCS2 encoding;

· LIKE4 - for UCS4 encoding.

An example of using this operator might look like this:

like"R%";

In this example, the last _name after the where keyword is the column from which the string values ​​are taken from to test, and "R %" is the condition to test. Distinctive feature of the LIKE operator is that it can include special wildcard characters (metacharacters) in the condition. There are only two wildcards for this operator:

• % - represents any sequence of zero or more characters. In this case, the value "%" will never be equal to NULL (IS NULL is used to check such values);
• _ - represents any single character.

In our example, we used the % wildcard, so the query will return all employees whose last name starts with R .

If we want the percent sign (%) and underscore (_) to be treated by Oracle as normal searchable characters (and not as wildcards), we have the ESCAPE parameter at our disposal. With this option, you can define special character, after which the wildcard character will be considered normal:

select last_name from hr.employees where last_name like"R\%" ESCAPE "\";

In our example, we have defined the character \ as a wildcard override, and if we have an employee with the last name R%, then the query will definitely return it. The most common use of the wildcard escape character is the backslash (\), which is the most familiar value for C programmers, but you can use any character by defining it with ESCAPE .

The LIKE condition is not difficult to use, but it has a very limited set of possibilities. Much larger functionality provides a REGEXP _LIKE condition that allows you to specify a search condition using standard POSIX-compatible regular expressions. Regular expressions are a very large topic that is beyond the scope of this tutorial. For a complete reference on regular expressions supported by Oracle, see Appendix C of the SQL Reference book in the Oracle documentation. Here we give only a simple example:

SELECT first_name FROM employees

WHERE REGEXP_LIKE (first_name, "^Ste(v|ph)en$");

In this example, we return information about all employees whose name starts with Ste , ends with en , and has either v or ph in between. The result might look like this:

Steven

Steven

Compares a string expression with a pattern in an SQL expression.

Syntax

expression Like "sample"

Operator syntax Like includes the following components:

Remarks

Operator Like can be used to search for field values ​​that match the specified pattern. As template you can specify the full value (for example, Like “Smith” ) or use wildcards to get a range of values ​​(for example, Like “Sm*”) ).

In expressions, the operator Like can be used to compare a field value with a string expression. For example, if in SQL query specify Like “C*” , the query will return all field values ​​starting with the letter C. In a query with parameters, you can prompt the user to enter a search pattern.

The following example returns data that starts with the letter P followed by any letter from A to F and three digits:

Like “P###”

The following table shows how to use the operator Like to check against different patterns:

Match type	Sample	Conformity	No match (returns "True")
Multiple characters		aa, aBa, aBBBA
	abc, AABB, Cab
Special characters
Multiple characters		abcdeyo, abc
One character		aaa, a3a, aba
One digit		a0a, a1a, a2a
Character range
Out of range
Mixed

The usefulness of the LIKE clause comes from the generalization operators it supports. The LIKE clause returns the boolean value TRUE if the comparison finds a match.

For the LIKE clause to function, the case sensitivity of a particular DBMS is very important. For example, Microsoft SQL Server is case-insensitive by default (although it can be configured accordingly). So SQL Server will treat DAD and dad rows as the same. On the other hand, the Oracle platform is case sensitive, and the strings DAD and dad will be different here. Here is an example to better illustrate this point.

SELECT * FROM authors WHERE lname LIKE "LARS%"

This query for Microsoft SQL Server will retrieve records from the authors table where the last name (lname) is "larson" or "lars" even though the search is defined using uppercase ("LARS%") in the query. In Oracle, this query will not find the last names "Larson" or "Lars" because Oracle does a case-sensitive comparison.

DB2

The DB2 platform supports the ANSI SQL 2003 LIKE clause syntax. The wildcards % and the underscore (_) are supported. Cancellation sequences are supported.

The DB2 platform is case sensitive, so the implementation of the LIKE clause here is fully case sensitive. To always compare case-insensitive values, use the UPPER or TRANSLATE function. In addition, DB2 implicitly converts the code page of a string template or escape sequence to an expression code page, unless they are specified with a FOR BIT DATA clause.

MySQL

The MySQL platform supports the ANSI syntax for the LIKE clause. The wildcards % and the underscore (_) are supported. The ESCAPE clause is also supported.

In addition, MySQL supports the REGEXP and NOT RLIKE special functions used in regular expression validation. MySQL after version 3.23.4 is case insensitive by default.

Oracle

The Oracle platform supports the ANSI syntax for the LIKE clause. The wildcards % and the underscore (_) are supported. The ESCAPE clause is also supported. The syntax for the LIKE clause in Oracle is as follows.

WHERE expression (LIKE | LIKEC | LIKE2 |

LIKE4) string_pattern

Oracle-specific syntax elements have the following meanings.

used full set UNICODE characters.

The UNICODE USC2 character set is used.

The UNICODE USC4 character set is used.

Because the Oracle platform is case-sensitive, you must include an expression, string_pattern, or both, in the UPPER function. In this case, you will always compare what you need.

PostgreSQL

The PostgreSQL platform supports the ANSI LIKE clause syntax. The wildcards % and the underscore (_) are supported. Cancellation sequences are also supported.

PostgreSQL is case sensitive by default. For case-insensitive comparison, PostgreSQL provides the ILIKE keyword. You can also use the - operator as equivalent to LIKE and -* as equivalent to ILIKE, and !- and !-* as equivalent to NOT LIKE and NOT ILIKE respectively. These are all extensions to the ANSI standard that exist in PostgreSQL.

For example, the following queries are functionally equivalent.

SELECT * FROM authors WHERE city LIKE "%ville"; SELECT * FROM authors WHERE city -- ^ville";

Because these examples use lower case, you may encounter a case-sensitive issue. That is, the query searches for the string "%ville" in lowercase, and the table may contain uppercase values ​​that will not be included in the results - "BROWNSVILLE", "NASHVILLE", "HUNTSVILLE". This problem can be solved as shown in the following example.

Converting values ​​to upper case

SELECT * FROM authors WHERE city LIKE UPPER("%ville");

Do a case-insensitive comparison SELECT * FROM authors WHERE city ~~* "%ville";

SELECT * FROM authors WHERE city LIKE "%ville";

You should be aware (although this is beyond the scope of this book) that PostgreSQL also supports POSIX regular expressions. Details are given in the description of the platform.

SQL Server

The SQL Server platform supports the ANSI LIKE clause syntax. Cancellation sequences are supported. The following additional generalization operators are also supported.

Matches any value from the specified set, such as , or range, such as [k-n].

[L]- matches any character not in the specified set or range.

Using additional generalizing SQL statements server you get additional features. For example, you might want to retrieve records for authors whose last names are Carson, Carsen, Karson, or Karsen.

SELECT * FROM authors WHERE au_lname LIKE "arsn"

SELECT * FROM authors WHERE au_lname LIKE "arsn"

Topic 3.2. Selecting data using the SELECT clause

All SQL statements designed to select data from existing database tables begin with the keyword (operator) SELECT (select). To refine the query, additional operators are used, such as FROM (from), WHERE (where), etc.

The simplest syntax for a SELECT query is:

SELECT<список столбцов>

FROM<список таблиц>

Operators that may be missing from the query are listed. These operators are used to refine the data fetch request:

p WHERE(where) - indicates the records that should be included in the resulting table (record filter);

p GROUP BY(group by) - groups records by the values ​​of certain columns;

p HAVING(having, subject to) - indicates the groups of records that should be included in the resulting table (group filter);

p ORDER BY(sort by) – sorts (arranges) records.

Operators SELECT and FROM are mandatory. The SELECT keyword tells the database that the given clause is a request to retrieve information. After the word SELECT through "," the names of the columns whose content is requested are listed. The FROM word is followed by a list of table names (separated by ",") from which information is retrieved.

Example:

SELECT NAME, SURNAME

The above query fetches all NAME and SURNAME values ​​from the STUDENTS table. The result is a table with 2 columns.

The order of the columns in this table matches the order of the fields specified in the query, not their order in the STUDENTS table.

If you need to get all the columns of the table, then instead of a list of columns, it is enough to specify the symbol ( *) .

Example:

SELECT *

Immediately after the SELECT statement before the list of columns, you can apply keywords ALL (all) and DISTINCT (different), which specify which records to represent in the resulting table. If these keywords are not used, it is assumed that all records should be selected (which is also the same as using the ALL keyword). When DISTINCT is used, only unique records are presented in the resulting table. In this case, if there are several identical records in the source table, then only the first one is selected from them.

Example:

SELECT DISTINCT CITY

If more than one table is specified in the FROM clause, then the column names in the SELECT clause must contain prefixes, indicating which table they belong to. The prefix is ​​separated from the column name by a dot.

Example:

Expression STUDENTS.NAME means column NAME from the table STUDENTS

The column headers in the resulting table can be redefined at your discretion by assigning the so-called aliases. To do this, in the list of columns after the corresponding column, write an expression of the form: AS column-header

Example:

SELECT NAME AS First name, SURNAME AS Last name

Aliases can also be specified for each table after the FROM keyword. To do this, it is enough to specify an alias separated by a space immediately after the name of the corresponding table. Table aliases that are shorter than their names are useful in complex queries.

Example:

SELECT T1.NAME , T1.SURNAME, T2.SUM_STIPEND

FROM STUDENTS T1, STIPEND T2;

WHERE statement

The search conditions in the WHERE clause are boolean expressions, i.e. take one of three possible values: true, false and NULL (this happens when some of the elements in the expression are NULL). Thus, in SQL we are dealing with three-valued logic.

When compiling logical expressions, special keywords and symbols of comparison operations are used, which are called predicates:

ü comparison predicates: (=), (<), (>), (<>), (<=), (>=);

ü LIKE, NOT LIKE;

ü ALL, SOME, ANY;

Example:

WHERE SURNAME = "Petrov";

As a result of the query execution, a table of one column will be obtained, containing the names of all students named Petrov, who are in the STUDENTS table.

Example:

Write a request for the names and surnames of students studying in the 3rd year and receiving a scholarship:

SELECT NAME, SURNAME

WHERE KURS=3 AND STIPEND>0;

BETWEEN Operator

The BETWEEN (between) predicate allows you to specify an expression for checking whether a value is in the range defined by the boundary values.

Example:

SELECT SUBJECT_NAME

WHERE HOURS BETWEEN 30 AND 40;

Output the names of subjects, the study of which is given the number of hours in the range from 30 to 40.

The limit values ​​are within the range of values ​​against which the comparison is made.

The equivalent of the above is an expression with comparison predicates:

SELECT SUBJECT_NAME

WHERE HOURS>30 AND HOURS<40;

In addition to numeric type data, expressions with BETWEEN can use data of the following types: character, bit, date-time.

IN and NOT IN operators

The IN (in) and NOT IN (not in) predicates are used to test whether a value is in a given list of values.

A predicate constructed using IN is considered true if the value of the field whose name is indicated to the left of IN matches one of the values ​​in the list.

Example:

SELECT STUDENT_ID

WHERE MARK IN (4, 5);

Get from the EXAM_MARKS table information about students who have only 4 and 5 exam grades.

NOT IN- does not match any of the values

Example:

SELECT STUDENT_ID

WHERE MARK NOT IN(0, 1, 2, 3);

Get information about students who do not have unsatisfactory exam grades from the EXAM_MARKS table.

Operators LIKE and NOT LIKE

The LIKE (similar) and NOT LIKE (not similar) predicates are used to test for partial matching of character strings. This operator looks at the string values ​​of the fields to determine whether the string specified in the LIKE operator is included in the character string_value of the field being checked.

The criterion of partial compliance is specified using two wildcard characters: percent sign (%) and underscore (_). The percent sign (%) means any set of characters, including the empty one, and the underscore (_) means any single character.

Example:

SELECT *

WHERE SURNAME LIKE "P%";

The query results in a table containing data on students whose last name begins with the letter "P".

If you want to exclude all students with the last name "Petrov", you must run the following query:

SELECT *

WHERE SURNAME NOT LIKE "Petrov";

If it is necessary to include the underscore or percent characters themselves in the "sample", it is necessary that such characters are not perceived by the SQL interpreter as mask characters. In order for a percent or underscore sign to be taken literally, you must precede it with a special control character. This character can be defined arbitrarily, as long as it does not occur as a data element.

Example:

SELECT Name, Address, Discount_Percent

FROM Clients

WHERE Percent_discount LIKE "20#%"

Here, the ESCAPE keyword is followed by the character that is used as a control. In the same way, you can disable the control character itself.

Example:

LIKE "_ \ _P"

In this expression, the "\" character is declared an ESC character using the ESCAPE keyword. The 1st character "_" will match, as before, any character in the string being checked, and the 2nd character "_" will be interpreted literally, like a regular underscore character.

IS NULL Operator

The IS NULL predicate is used to identify records where a particular column has no value.

Example:

You can get customer records for which no address is specified using the following query:

SELECT Name, Address, Region

FROM Clients

WHERE Address IS NULL;

To retrieve records where the "Address" column contains some specific value (i.e., non-null), you can use a similar expression, but with the logical NOT (not) operator:

SELECT Name, Address, Region

FROM Clients

WHERE Address IS NOT NULL;

Comparison predicates should not be used with NULL, such as "Address=NULL"