Ms sql like in syntax. LIKE and NOT LIKE operators

The utility of the LIKE clause is based on the generic operators it supports. The LIKE clause returns the Boolean value TRUE if the comparison finds matching values.

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

SELECT * FROM authors WHERE lname LIKE "LARS%"

This Microsoft SQL Server query will retrieve records from the authors table where the last name (lname) is of the form "larson" or "lars", although the query specifies the lookup using upper case ("LARS%"). 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 standard LIKE clause syntax. The wildcard characters % and the underscore (_) are supported. Cancellation sequences are supported.

The DB2 platform is case-sensitive, so the LIKE clause implementation here is completely case-sensitive. To ensure that values ​​in different registers are always compared in a case-insensitive manner, you must use the UPPER or TRANSLATE function. In addition, DB2 implicitly converts the code page of a string pattern or cancellation sequence to the code page of an expression unless it is defined with a FOR BIT DATA clause.

MySQL

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

In addition, MySQL supports special functions REGEXP and NOT RLIKE, which are used when validating regular expressions. MySQL after version 3.23.4 is case insensitive by default.

Oracle

The Oracle platform supports ANSI syntax for the LIKE clause. The wildcard characters % 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 ANSI LIKE clause syntax. The wildcard characters % and the underscore (_) are supported. Cancellation sequences are also supported.

PostgreSQL is case sensitive by default. For case-insensitive comparison, PostgreSQL has the ILIKE keyword. You can also use the - operator as the equivalent of LIKE and -* as the equivalent of ILIKE, and !- and !-* as the equivalent of NOT LIKE and NOT ILIKE respectively. These are all ANSI standard extensions 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 lowercase, you may encounter a case sensitivity 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 uppercase

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

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

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

SQL Server

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

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

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

By using SQL Server's additional wildcard statements, you get additional features. For example, you can 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"

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

Very often in practical work there is a need to conduct a search by a set of characters anywhere in a column - for example, to search for all records with product names containing a word, or to search for a word in both the 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 say right away that LIKE and REGEXP _LIKE are used only in relatively simple situations. If you need to search based on grammar, filtering noise words, etc., then you should consider using full-text search.

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

· "regular" 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 ​​to be tested are taken, and "R %" is the condition to be tested. Distinctive feature The advantage of the LIKE operator is that it can include special wildcard characters (metacharacters) in the condition. There are only two wildcards available 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 character, so the query will return all employees whose last name begins with R .

If we want the percent (%) and underscore (_) symbols to be treated by Oracle as regular searchable characters (rather than as wildcard characters), we have the ESCAPE parameter. Using this parameter you can determine special character, after which the wildcard will be considered normal:

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

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

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

SELECT first_name FROM employees

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

In this example, we are returning 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:

Notes

Operator Like can be used to find field values ​​that match a specified pattern. As template you can specify a 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 begins 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 various patterns:

Topic 3.2. Retrieving Data Using the SELECT Clause

All SQL expressions intended for retrieving 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 not be present in the request are indicated. These operators are used to refine the request for data retrieval:

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, provided) – indicates 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 this statement is a request to retrieve information. After the word SELECT, the names of the columns whose contents are requested are listed through ",". After the word FROM, a list of table names is indicated (through ",") from which information is retrieved.

Example:

SELECT NAME, SURNAME

The following query retrieves all NAME and SURNAME values ​​from the STUDENTS table. The result is a table consisting of 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 a table, then instead of a list of columns, just specify the symbol ( *) .

Example:

SELECT *

Right after SELECT statement you can use the ALL keywords before the list of columns (All) and DISTINCT (different), which specify which records to present in the resulting table. If these keywords are not used, the implication is that all records should be selected (which is also the same as using the ALL keyword). If DISTINCT is used, only unique records. Moreover, if the source table contains several identical records, then only the first one is selected.

Example:

SELECT DISTINCT CITY

If the FROM clause specifies more than one table, the SELECT clause must contain column names 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 headings in the resulting table can be redefined at your discretion by assigning so-called pseudonyms. To do this, in the list of columns after the corresponding column, write an expression like: 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, just specify the alias separated by a space immediately after the name of the corresponding table. Table aliases, which are shorter than their names, are useful for complex queries.

Example:

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

FROM STUDENTS T1, STIPEND T2;

WHERE clause

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 elements in the expression are NULL). So in SQL we are dealing with three-valued logic.

When composing 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 executing the query, a table will be obtained from one column containing the names of all students with the last name Petrov, who are in the STUDENTS table.

Example:

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

SELECT NAME, SURNAME

WHERE KURS=3 AND STIPEND>0;

Operator BETWEEN

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

Example:

SELECT SUBJECT_NAME

WHERE HOURS BETWEEN 30 AND 40;

Display the names of subjects for which the number of hours in the range from 30 to 40 is allocated.

The limit values ​​are included in the range of values ​​with which the comparison is made.

An expression with comparison predicates is equivalent to the following:

SELECT SUBJECT_NAME

WHERE HOURS>30 AND HOURS<40;

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

IN and NOT IN operators

The predicates IN (in) and NOT IN (not in) are used to check the occurrence of a value 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 information from the EXAM_MARKS table about students who have exam grades of only 4 and 5.

NOT IN– does not match any of the values

Example:

SELECT STUDENT_ID

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

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

LIKE and NOT LIKE operators

The LIKE and NOT LIKE predicates are used to test partial matches 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 partial matching criterion is specified using two mask characters: percent sign (%) and underscore (_). The percent sign (%) signifies any set of characters, including the empty character, and the underscore (_) signifies any single character.

Example:

SELECT *

WHERE SURNAME LIKE "P%";

As a result of the query, a table containing data about students whose last name begins with the letter “P” is returned.

If you need 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 underscores or percent characters in the "pattern", it is necessary that such characters are not perceived by the SQL interpreter as wildcard characters. For a percent or underscore to be taken literally, it must be preceded by a special control character. This character can be defined arbitrarily, as long as it does not appear as a data element.

Example:

SELECT Name, Address, Discount_Percentage

FROM Clients

WHERE Discount_percentage LIKE "20#%"

Here, the ESCAPE keyword is followed by the symbol 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 as 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 an ordinary underscore character.

IS NULL operator

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

Example:

You can get records about clients for whom the address is not specified using the following request:

SELECT Name, Address, Region

FROM Clients

WHERE Address IS NULL;

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

SELECT Name, Address, Region

FROM Clients

WHERE Address IS NOT NULL;

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

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 version of the database

In this tutorial we will use the well known Northwind database.

Below is a selection from the "Customers" table:

Examples of SQL LIKE operator

The following SQL statement selects all clients 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'll 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 "land" pattern:

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

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