Как найти совпадение sql

Перевод второй части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».

В первой части статьи мы рассмотрели такие темы:

• Настройка базы данных
• Создание пользователей
• Вставка пользователей
• Фильтрация данных при помощи WHERE
• Логические операторы (AND / OR / NOT)
• Операторы сравнения (<, >, <=, >=)
• Арифметические операторы (+, -, *, /, %)

В этой части мы рассмотрим:

• Операторы для проверки существования (IN / NOT IN)
• Частичное совпадение — использование LIKE
• Работа с отсутствующими данными (NULL)
• Использование IS NULL и IS NOT NULL
• Операторы сравнения при работе с датами и временем
• Проверка существования с использованием EXISTS / NOT EXISTS
• Поразрядные операторы

Операторы для проверки существования (IN / NOT IN)

Если мы хотим проверить, есть ли определенное значение в списке значений, мы можем воспользоваться операторами IN или NOT IN:

SELECT *
FROM users
WHERE first_name IN ('John', 'Jane', 'Rick');

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28
  6 | John       | Jacobs    | jjacobs@corp.net    |  56
  7 | Rick       | Fuller    | fullman@hotmail.com |  16
(4 rows)

Аналогично, для отрицания используется NOT IN:

SELECT *
FROM users
WHERE first_name NOT IN ('John', 'Jane', 'Rick');

 id | first_name | last_name |      email       | age
----+------------+-----------+------------------+-----
  3 | Xavier     | Wills     | xavier@wills.io  |  35
  4 | Bev        | Scott     | bev@bevscott.com |  16
  5 | Bree       | Jensen    | bjensen@corp.net |  42
(3 rows)

Частичное совпадение — использование LIKE

Иногда нам нужно найти строки, основываясь на частичном совпадении.

Допустим, мы хотим найти всех пользователей, которые зарегистрировались в нашем приложении при помощи адреса Gmail. Мы можем поискать частичное совпадение в столбце, используя ключевое слово LIKE. Также при этом можно использовать групповой символ — %.

Поиск пользователей, чей email оканчивается на gmail.com:

SELECT *
FROM users
WHERE email LIKE '%gmail.com';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
(1 row)

Строка %gmail.com означает «совпадение со всем, что кончается на gmail.com».

Если мы посмотрим на данные наших пользователей, мы заметим, что среди них только у двоих адрес электронной почты кончается на gmail.com:

('John', 'Smith', 'johnsmith@gmail.com', 25),
('Jane', 'Doe', 'janedoe@Gmail.com', 28),

Но в email Джейн указана заглавная «G». Предыдущий запрос не выберет эту запись, потому что мы ищем точное совпадение с gmail.com, а там «g» в нижнем регистре.

Чтобы поиск не зависел от регистра, нужно заменить LIKE на ILIKE:

SELECT *
FROM users
WHERE email ILIKE '%gmail.com';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28
(2 rows)

Групповой символ % в начале строки означает, что вернуть нужно все, что заканчивается на «gmail.com». Это может быть и ob.jones+12345@gmail.com, и asdflkasdflkj@gmail.com — главное, чтобы в конце стояло «gmail.com».

Мы также можем использовать столько групповых символов, сколько нам нужно.

Например, поиск %j%o% вернет любой email-адрес, соответствующий шаблону «<все-что-угодно>, за чем следует j, за чем следует <все-что-угодно>, за чем следует o, за чем следует <все-что-угодно>»:

SELECT * FROM users WHERE email ILIKE '%j%o%';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28
  5 | Bree       | Jensen    | bjensen@corp.net    |  42
  6 | John       | Jacobs    | jjacobs@corp.net    |  56
(4 rows)

От редакции Techrocks: также рекомендуем статью «Топ-30 вопросов по SQL на технических собеседованиях».

Работа с отсутствующими данными (NULL)

Давайте посмотрим, как быть со столбцами и строками, где нет данных.

Для этого давайте добавим в нашу таблицу users еще один столбец: first_paid_at.

Этот новый столбец будет TIMESTAMP (подобно datetime в других языках) и будет представлять дату и время, когда пользователь впервые заплатил нам за наше приложение. Может, мы хотим послать ему открытку и цветы в честь годовщины.

Мы могли бы стереть нашу таблицу users, введя DROP TABLE users, и пересоздать ее заново, но таким образом мы удалили бы все данные в таблице.

Чтобы изменить таблицу, не стирая ее и не лишаясь данных, можно использовать ALTER TABLE:

ALTER TABLE users ADD COLUMN first_paid_at TIMESTAMP;

Эта команда возвращает результат ALTER TABLE, так что наш запрос ALTER сработал успешно.

Если мы теперь запросим нашу таблицу users, мы заметим, что теперь в ней появился новый столбец без данных:

SELECT * FROM users;

 id | first_name | last_name |        email        | age | first_paid_at
----+------------+-----------+---------------------+-----+---------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 |
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28 |
  3 | Xavier     | Wills     | xavier@wills.io     |  35 |
  4 | Bev        | Scott     | bev@bevscott.com    |  16 |
  5 | Bree       | Jensen    | bjensen@corp.net    |  42 |
  6 | John       | Jacobs    | jjacobs@corp.net    |  56 |
  7 | Rick       | Fuller    | fullman@hotmail.com |  16 |
(7 rows)

Наш столбец first_paid_at пуст, и результат нашего psql-запроса показывает, что это пустой столбец. Технически он не пустой: в нем содержится специальное значение, которое psql просто не показывает в выводе — NULL.

NULL это специальное значение в базах данных. Это отсутствие значения, и оно ведет себя не так, как можно было бы ожидать.

Чтобы это продемонстрировать, давайте посмотрим на простой SELECT:

SELECT
  1 = 1,
  1 = 2;

 ?column? | ?column?
----------+----------
 t        | f
(1 row)

Здесь мы просто выбрали 1 = 1 и 1 = 2. Как мы и ожидали, результат этих двух предложений — t и f (или TRUE и FALSE). 1 равен 1, но 1 не равен 2.

Теперь давайте попробуем проделать то же самое с NULL:

SELECT 1 = NULL;

 ?column?
----------

(1 row)

Мы могли ожидать, что значением будет FALSE, но на деле возвращается значение NULL.

Чтобы еще лучше визуализировать NULL, давайте при помощи опции pset посмотрим, как psql отображает NULL-значения:

fcc=# pset null 'NULL'
Null display is "NULL".

Если мы запустим этот запрос еще раз, мы увидим в выводе ожидаемый нами NULL:

SELECT 1 = NULL;

 ?column?
----------
 NULL
(1 row)

Итак, 1 не равен NULL, а как насчет NULL = NULL?

SELECT NULL = NULL;

 ?column?
----------
 NULL
(1 row)

Довольно странно, однако NULL не равен NULL.

NULL лучше представлять себе как неизвестное значение. Равно ли неизвестное значение единице? Мы не знаем, оно же неизвестное. Равно ли неизвестное значение неизвестному значению? Опять же, мы этого не знаем. Это немного лучше поясняет, что такое NULL.

Использование IS NULL и IS NOT NULL

Мы не можем использовать с NULL оператор равенства, но мы можем пользоваться двумя специально созданными для этого операторами: IS NULL и IS NOT NULL.

SELECT
  NULL IS NULL,
  NULL IS NOT NULL;

 ?column? | ?column?
----------+----------
 t        | f
(1 row)

Эти значения ожидаемы: NULL IS NULL — истина, NULL IS NOT NULL — ложь.

Это все прекрасно и очень интересно, но как это применять на практике?

Что ж, для начала давайте заведем какие-то данные в нашем столбце first_paid_at:

UPDATE users SET first_paid_at = NOW() WHERE id = 1;
UPDATE 1

UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 month') WHERE id = 2;
UPDATE 1

UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 year') WHERE id = 3;
UPDATE 1

В приведенной выше инструкции UPDATE мы задали значения для столбца first_paid_at у троих разных пользователей: пользователю с ID 1 — текущее время (NOW()), пользователю с ID 2 — текущее время минус месяц, а пользователю с ID 3 — текущее время минус год.

Во-первых, давайте найдем пользователей, которые нам уже платили, и пользователей, которые пока этого не делали:

SELECT *
FROM users
WHERE first_paid_at IS NULL;

 id | first_name | last_name |        email        | age | first_paid_at
----+------------+-----------+---------------------+-----+---------------
  4 | Bev        | Scott     | bev@bevscott.com    |  16 | NULL
  5 | Bree       | Jensen    | bjensen@corp.net    |  42 | NULL
  6 | John       | Jacobs    | jjacobs@corp.net    |  56 | NULL
  7 | Rick       | Fuller    | fullman@hotmail.com |  16 | NULL
(4 rows)

SELECT *
FROM users
WHERE first_paid_at IS NOT NULL;

 id | first_name | last_name |        email        | age |       first_paid_at
----+------------+-----------+---------------------+-----+----------------------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 | 2020-08-11 20:49:17.230517
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28 | 2020-07-11 20:49:17.233124
  3 | Xavier     | Wills     | xavier@wills.io     |  35 | 2019-08-11 20:49:17.23488
(3 rows)

Операторы сравнения при работе с датами и временем

Теперь, когда у нас есть кое-какие данные, давайте используем те же операторы сравнения применительно к новому полю TIMESTAMP.

Попробуем найти пользователей, которые совершили платеж на протяжении последней недели. Для этого мы можем взять текущее время (NOW()) и вычесть из него одну неделю при помощи ключевого слова INTERVAL:

SELECT *
FROM users
WHERE first_paid_at > (NOW() - INTERVAL '1 week');

 id | first_name | last_name |        email        | age |       first_paid_at
----+------------+-----------+---------------------+-----+----------------------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 | 2020-08-11 20:49:17.230517
(1 row)

Мы также можем использовать другой интервал, например, последние три месяца:

SELECT *
FROM users
WHERE first_paid_at < (NOW() - INTERVAL '3 months');

 id | first_name | last_name |      email      | age |       first_paid_at
----+------------+-----------+-----------------+-----+---------------------------
  3 | Xavier     | Wills     | xavier@wills.io |  35 | 2019-08-11 20:49:17.23488
(1 row)

Теперь давайте найдем пользователей, которые совершали платеж в промежутке от одного до шести месяцев назад.

Мы можем скомбинировать наши условия, используя AND, но вместо использования операторов < и > давайте используем ключевое слово BETWEEN:

SELECT *
FROM users
WHERE first_paid_at BETWEEN (NOW() - INTERVAL '6 month')
  AND (NOW() - INTERVAL '1 month');
  
 id | first_name | last_name |       email       | age |       first_paid_at
----+------------+-----------+-------------------+-----+----------------------------
  2 | Jane       | Doe       | janedoe@Gmail.com |  28 | 2020-07-11 20:49:17.233124
(1 row)

Проверка существования с использованием EXISTS / NOT EXISTS

Другой способ проверить существование (наличие) значения — использовать EXISTS и NOT EXISTS.

Эти операторы фильтруют строки, проверяя существование или несуществование условия. Это условие обычно является запросом к другой таблице.

Чтобы это продемонстрировать, давайте создадим новую таблицу под названием posts. В этой таблице будут содержаться посты, котоыре пользователь может делать в нашей системе.

CREATE TABLE posts(
  id SERIAL PRIMARY KEY,
  body TEXT NOT NULL,
  user_id INTEGER REFERENCES users NOT NULL
);

Это простая таблица. Она содержит только ID, поле для хранения текста поста (body) и ссылку на пользователя, который написал этот пост (user_id).

Давайте добавим в новую таблицу некоторые данные:

INSERT INTO posts(body, user_id) VALUES
('Here is post 1', 1),
('Here is post 2', 1),
('Here is post 3', 2),
('Here is post 4', 3);

Согласно добавленным данными, у пользователя с ID 1 есть два поста, у пользователя с ID 2 — один пост, у пользователя с ID 3 — тоже один пост.

Чтобы найти пользователей, у которых есть посты, мы можем использовать ключевое слово EXISTS.

EXISTS принимает подзапрос. Если этот подзапрос возвращает что-либо (даже строку со значением NULL), база данных включит эту строку в результат.

EXISTS проверяет лишь существование строки из подзапроса, ему не важно, что именно содержится в этой строке.

Вот пример выборки пользователей, имеющих посты:

SELECT *
FROM users
WHERE EXISTS (
  SELECT 1
  FROM posts
  WHERE posts.user_id = users.id
);

 id | first_name | last_name |        email        | age |       first_paid_at
----+------------+-----------+---------------------+-----+----------------------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 | 2020-08-11 20:49:17.230517
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28 | 2020-07-11 20:49:17.233124
  3 | Xavier     | Wills     | xavier@wills.io     |  35 | 2019-08-11 20:49:17.23488
(3 rows)

Как и ождилалось, мы получили пользователей с ID 1, 2 и 3.

Наш подзапрос EXISTS проверяет записи в таблице posts, где user_id поста совпадает со столбцом id таблицы users. Мы вернули 1 в нашем SELECT, потому что здесь мы можем вернуть что угодно: база данных просто хочет видеть, что что-то вернулось.

Аналогично, мы можем найти пользователей, у которых нет постов. Для этого нужно заменить EXISTS на NOT EXISTS:

SELECT *
FROM users
WHERE NOT EXISTS (
  SELECT 1
  FROM posts
  WHERE posts.user_id = users.id
);

 id | first_name | last_name |        email        | age | first_paid_at
----+------------+-----------+---------------------+-----+---------------
  4 | Bev        | Scott     | bev@bevscott.com    |  16 | NULL
  5 | Bree       | Jensen    | bjensen@corp.net    |  42 | NULL
  6 | John       | Jacobs    | jjacobs@corp.net    |  56 | NULL
  7 | Rick       | Fuller    | fullman@hotmail.com |  16 | NULL
(4 rows)

Наконец, мы можем переписать наш запрос и использовать IN или NOT IN вместо EXISTS или NOT EXISTS:

SELECT *
FROM users
WHERE users.id IN (
  SELECT user_id
  FROM posts
);

Технически это сработает, но вообще, если вы проверяете существование другое записи, более производительно будет использовать EXISTS. Операторы IN и NOT IN в целом лучше применять для проверки значения в статическом списке, как мы делали ранее:

SELECT *
FROM users
WHERE first_name IN ('John', 'Jane', 'Rick');

Поразрядные операторы

Хотя на практике поразрядные операторы используются нечасто, для полноты картины давайте рассмотрим простой пример.

Если мы по какой-то причине хотим посмотреть возраст наших пользователей в бинарном виде и поиграться с перестановкой битов, мы можем использовать поразрядные операторы.

В качестве примера давайте рассмотрим поразрядный оператор «and»: &.

SELECT age::bit(8) & '11111111' FROM users;

 ?column?
----------
 00010000
 00101010
 00111000
 00010000
 00011001
 00011100
 00100011
(7 rows)

Чтобы осуществить поразрядную операцию, нам сначала нужно преобразовать значения в нашем столбце age из целых чисел в бинарный формат. В данном случае мы использовали ::bit(8) и получили восьмибитовые строки.

Далее мы можем «сложить» результат в бинарном формате с другой строкой в бинарном формате — 11111111. Поскольку бинарный AND возвращает единицу только если оба бита это единицы, эта добавочная строка делает вывод интересным.

Практически все остальные поразрядные операторы используют тот же формат:

SELECT age::bit(8) | '11111111' FROM users;    -- поразрядный OR
SELECT age::bit(8) # '11111111' FROM users;    -- поразрядный XOR
SELECT age::bit(8) << '00000001' FROM users;   -- поразрядный сдвиг влево
SELECT age::bit(8) >> '00000001' FROM users;   -- поразрядный сдвиг вправо

Поразрядный оператор «not» (~) немного отличается. Он применяется к одному термину, так же, как и обычный оператор NOT:

SELECT ~age::bit(8) FROM users;

 ?column?
----------
 11101111
 11010101
 11000111
 11101111
 11100110
 11100011
 11011100
(7 rows)

И, наконец, самый полезный из поразрядных операторов: конкатенация.

Этот оператор обычно используется для склейки вместе строк текста. Например, если мы хотим составить вычисленное «полное имя» для пользователей, мы можем воспользоваться конкатенацией:

SELECT first_name || ' ' || last_name AS name
FROM users;

     name
--------------
 Bev Scott
 Bree Jensen
 John Jacobs
 Rick Fuller
 John Smith
 Jane Doe
 Xavier Wills
(7 rows)

Здесь мы для создания значения name сконкатенировали (скомбинировали) first_name, пробел (' ') и last_name.

Заключение

Итак, мы рассмотрели практически все операторы фильтрации, котоыре вам могут понадобиться в работе!

Есть еще несколько, о которых мы не упоминали, но они либо используются не слишком часто, либо используются точно так же, как те, что мы разобрали, так что у вас не должно возникнуть проблем с ними.

---

От редакции Techrocks: возможно, вам будет интересна еще одна статья того же автора: SQL JOIN: руководство по объединению таблиц.

If you are avoiding stored procedures like the plague, or are unable to do a mysql_dump due to permissions, or running into other various reasons.

I would suggest a three-step approach like this:

1) Where this query builds a bunch of queries as a result set.

# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER 
# ** USE AN ALTERNATE BACKUP **

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' LIKE '%stuff%';') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     
        (
            A.DATA_TYPE LIKE '%text%'
        OR  
            A.DATA_TYPE LIKE '%char%'
        )
;

.

# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' IN ('%1234567890%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;

.

# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT 
    CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN ('%someText%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE LIKE '%blob%'
;

Results should look like this:

2) You can then just Right Click and use the Copy Row (tab-separated)

3) Paste results in a new query window and run to your heart’s content.

Detail: I exclude system schema’s that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas checked.

I did this to provide a quick way to ANALYZE an entire HOST or DB if needed or to run OPTIMIZE statements to support performance improvements.

I’m sure there are different ways you may go about doing this but here’s what works for me:

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

Tested On MySQL Version: 5.6.23

WARNING: DO NOT RUN THIS IF:

1. You are concerned with causing Table-locks (keep an eye on your client-connections)

2. You are unsure about what you are doing.

3. You are trying to anger you DBA. (you may have people at your desk with the quickness.)

Cheers, Jay ;-]

Being able to do complex queries can be really useful in SQL.

In this article, we’ll look at how you can use the Contains String query.

SQL Patterns

SQL patterns are useful for pattern matching, instead of using literal comparisons. They have a more limited syntax than RegEx, but they’re more universal through the various SQL versions.

SQL patterns use the LIKE and NOT LIKE operators and the metacharacters (characters that stand for something other than themselves) % and _.

The operators are used like this: column_name LIKE pattern.

Character	Meaning
%	Any sequence of characters
_	Exactly one character

You can use these characters in a wide variety of use-cases. Here are some examples:

example pattern	usage
re%	Strings that begin with a specific substring
%re	Strings that end with a specific substring
%re%	Strings that have a specific substring anywhere in the string
%re_	Strings that have a specific substring at a specific position from the end¹
__re%	Strings that have a specific substring at a specific position from the beginning²

¹ (in the example, second to last and third to last characters are determined)
² (in the example, third and fourth characters are determined)

SELECT name FROM planets
  WHERE name LIKE "%us";

Where planets is a table with the data of the solar system’s planets.

With this query you would get the below names of the planets that end with «us».

name
Venus
Uranus

The NOT LIKE operator finds all strings that do not match the pattern.

Let’s use it in an example too.

SELECT name FROM planets
  WHERE name NOT LIKE "%u%";

With this query you get all planets whose names don’t contain the letter u, like below.

name
Earth
Mars

Alternative to the LIKE operator in SQL

Depending on the SQL flavour you are using, you might also be able to use the SIMILAR TO operator. You can use it in addition to or in place of LIKE.

The SQL SIMILAR TO operator

The SIMILAR TO operator works in a pretty similar way to the LIKE operator, including which metacharacters are available. You can use the % operator for any number of characters, and the _ operator for exactly one character.

Let’s take the example used with LIKE and let’s use it here too.

SELECT name FROM planets
  WHERE name SIMILAR TO "%us";

You can use this operator with NOT in front to have the opposite effect. This is how you would write the example we used before using SIMILAR TO instead:

SELECT name FROM planets
  WHERE name NOT SIMILAR TO "%u%";

RegEx in SQL

What about if you need more complex pattern matching? Well, for that you need to use Regular Expressions.

What is RegEx?

RegEx on its own is a powerful tool that allows for flexible pattern recognition. You can use RegEx in many languages like PHP, Python, and also SQL.

RegEx lets you match patterns by character class (like all letters, or just vowels, or all digits), between alternatives, and other really flexible options. You will see them below.

What you can do with RegEx

You can do a lot of different things with RegEx patterns. To see a good variety, let’s use some of the examples presented in the RegEx freeCodeCamp Curriculum.

Keep in mind that the freeCodeCamp curriculum presents RegEx for JavaScript, so there is not a perfect match, and we need to convert the syntax. Still, it gives you a good overview of basic RegEx features, so let’s follow this curriculum so you can get a good idea of what RegEx can do.

Match Literal Strings

The easiest way to use RegEx it’s to use it to match an exact sequence of characters.

For example the regex "Kevin" will match all strings that contains those letters in that exact sequence, as «Kevin«, «Kevin is great», «this is my friend Kevin» and so on.

Match a Literal String with Different Possibilities

A regular expression can be used to match different possibilities using the character |. For example "yes|no|maybe" would match any string that contains one of the three sequence of characters, such as «maybe I will do it», «maybelline», «monologue», «yes, I will do it», «no, I don’t like it», and so on.

Match Anything with the Wildcard Period

The wildcard period . matches any character, for example "hu." would match anything that contains an h followed by an u followed by any character, such as «hug«, «hum«, «hub«, «huh«, but also «husband», «churros», «thumb», «shuttle» and so on.

Match Single Character with Multiple Possibilities

You can use a character class (or character set) to match a group of characters, for example "b[aiu]g" would match any string that contains a b, then one letter between a, i and u, and then a g, such as «bug«, «big«, «bag«, but also «cabbage», «ambigous», «ladybug«, and so on.

Match Letters of the Alphabet

You have seen above how you can match a group of characters with character classes, but if you want to match a long list of letters that is a lot of typing.

To avoid all that typing, you can define a range. For example you can match all letters between a and e with "[a-e]".

A regex like "[a-e]at" would match all strings that have in order one letter between a and e, then an a and then a t, such as «cat«, «bat» and «eat«, but also «birdbath», «bucatini», «date», and so on.

Match Numbers and Letters of the Alphabet

You can also use the hyphen to match numbers. For example "[0-5]" would match any number between 0 and 5, including 0 and 5.

You can also combine different ranges together in a single character set. For example "[a-z0-9]" would match all letters from a to z and all numbers from 0 to 5.

Match Single Characters Not Specified

You can also use a character set to exclude some characters from a match, these sets are called negated character sets.

You can create a negated character set by placing a caret character (^) after the opening bracket of the character class.

For example "[^aeiou]" matches all characters that are not vowels. It would match strings like «rythm» where no character is a vowel, or also «87 + 14».

Match Characters that Occur One or More Times

If you need to match a specific character or group of characters that can appear one or more times, you can use the character + after this character.

For example, "as+i" would match strings that contain one a followed by one or more s followed by one i, such as «occasional», «assiduous» and so on.

Match Characters that Occur Zero or More Times

If you can use + to match a character one or more times, there is also * to match a character zero or more times.

A regular expression such as "as*i" would match, other than «occasional» and «assiduous» also strings such as «aide».

Match Beginning String Patterns

Until now you have seen ways to match anywhere in the string, without the option to say where the match must be.

We use the character ^ to match the beginning of a string, for example a regex such as "^Ricky" would match «Ricky is my friend», but not «This is Ricky».

Match Ending String Patterns

Just as there’s a way to match the beginning of a string, there is also a way to match the end of a string.

You can use the character $ to match the end of a string, so for example "story$" would match any string that ends with «story», such as «This is a never ending story«, but not a string such a «Sometimes a story will have to end».

Match the Whole String

You can combine the two characters ^ and $ to match a whole string.

So, taking one of the previous examples, writing "b[aiu]g" can match both «big» and «bigger», but if instead you want to match only «big», «bag» and «bug», adding the two beginning and ending string characters ensures that there can’t be other characters in the string: "^b[aiu]g$". This pattern would match only «big», «bag» and «bug», and it doesn’t match «bigger» or «ambiguous».

Match All Letters and Numbers

You have seen before how to match characters with a character class.

There are a few predefined classes, called POSIX classes, that you can use instead. So if you want to match all letters and numbers like with "[0-9a-zA-Z]" you can instead write "[[:alphanum:]]".

Match Everything But Letters and Numbers

If instead you want to match anything that is not a letter of a number, you can use the alphanum POSIX class together with a negated character set: "[^[:alphanum:]].

Match All Numbers

You can also use a POSIX class to match all numbers instead of using "[0-9]", like so: "[[:digit:]]".

Match All Non-Numbers

You can use the digit POSIX class with a negated character set to match anything that is not a number, like so: "[^[:digit:]]".

Match Whitespace

You can match whitespace with the POSIX class "[[:blank:]]" or "[[:space:]]". The difference between these two classes is that the class blank matches only spaces and tabs, while space matches all blank characters, including carriage returns, new lines, form feeds, and vertical tabs.

Match Non-Whitespace Characters

You can match anything that is not a space or tab with "[^[:blank:]]".

And you can match anything that is not a whitespace, carriage return, tab, form feed, space, or vertical tab with "[^[:space:]]".

Specify Upper and Lower Number of Matches

You have seen before how to match one or more or zero or more characters. But sometimes you want to match a certain range of patterns.

For this you can use quantity specifiers.

Quantity specifiers are written with curly brackets ({ and }). You put two numbers separated by a comma in the curly bracket. The first is the lower number of patterns, the second is the upper number of patterns.

For example, if your pattern is "Oh{2,4} yes", then it would match strings like «Ohh yes» or «Ohhhh yes», but not «Oh yes» or «Ohhhhh yes».

Specify Exact Number of Matches

You can also use the quantity specifier other than for a range to specify an exact number of matches. You can do this by writing a single number inside the curly brackets.

So, if your pattern is "Oh{3} yes", then it would match only «Ohhh yes».

Check For Mixed Grouping of Characters

If you want to check for groups of characters using regular expressions you can do so using parenthesis.

For example, you may want to match both «Penguin» and «Pumpkin», you can do so with a regular expression like this: "P(engu|umpk)in".

Summary of RegEx patterns

You have seen a lot of regex options here. So now let’s put all of these, along with a few others, into easily consultable tables.

RegEx patterns

pattern	description
^	beginning of string
$	end of string
.	any character
( )	grouping characters
[abc]	any character inside the square brackets
[^abc]	any character not inside the square brackets
a|b|c	a OR b OR c
*	zero or more of the preceding element
+	one or more of the preceding element
{n}	n times the preceding element
{n,m}	between n and m times the preceding element

Posix classes

In the table below you can see the posix classes we saw above, as well as some others that you can use to create patterns.

Posix class	similar to	description
[:alnum:]	[a-zA-Z0-9]	Aphanumeric character
[:alpha:]	[a-zA-Z]	Alphabetic characters
[:blank:]		Spaces or tab characters
[:cntrl:]	[^[:print:]]	Control characters
[:digit:]	[0-9]	Numeric characters
[:graph:]	[^ [:ctrl:]]	All characters that have graphic rapresentation
[:lower:]	[a-z]	Lowercase alphabetic characters
[:print:]	[[:graph:][:space:]]	Graphic or spaces characters
[:punct:]		All graphic characters except letters and digits
[:space:]		Space, new line, tab, carriage return
[:upper:]	[A-Z]	Uppercase alphabetic characters
[:xdigit:]	[0-9a-fA-F]	Hexadecimal digits

Remember that when using a POSIX class, you always need to put it inside the square brackets of a character class (so you’ll have two pair of square brackets). For example, "a[[:digit:]]b" matches a0b, a1b and so on.

How to use RegEx patterns

Here you will see two kind of operators, REGEXP operators and POSIX operators. Just be aware that which operators you can use depends on the flavour of SQL you are using.

RegEx operators

RegEx operators are usually case insensitive, meaning that they don’t distinguish between uppercase and lowercase letters. So for them, a is equivalent to A. But you can change this default behaviour, so don’t take it for granted.

Operator	Description
REGEXP	Gives true if it matches the given pattern
NOT REGEXP	Gives true if the string doesn’t contain the given pattern

Posix operators

The other kind of operators you could have available are POSIX operators. Instead of being keywords, these are represented with punctuation, and can be case sensitive or insensitive.

operator	description
~	case sensitive, true if the pattern is contained in the string
!~	case sensitive, true if the pattern is not contained in the string
~*	case insensitive, true if the pattern is contained in the string
!~*	case insensitive, true if the pattern is not contained in the string

RegEx and Posix Examples

Let’s see how to use these operators and RegEx patterns in a query.

Example query 1

For this first example, you want to match a string in which the first character is an «s» or «p» and the second character is a vowel.

To do this, you can use the character class [sp] to match the first letter, and you can use the character class [aeiou] for the second letter in the string.

You also need to use the character to match the start of the string, ^, so all together you’ll write "^[sp][aeiou]".

You write the query below to get back the list of users whose names match the pattern.

SELECT name FROM users
  WHERE name REGEXP '^[sp][aeiou]';

And if the default case insensitive behaviour was changed, you would need to write a pattern that allows both uppercase and lowercase letters, like "^[spSP][aeiouAEIOU]" and use it in the query as below:

SELECT name FROM users
  WHERE name REGEXP '^[spSP][aeiouAEIOU]';

Or with the POSIX operator, in this case you could use the case insensitive operator, ~* and you would not need to write both upper case and lower case letters inside a character class. You could write the query as below.

SELECT name FROM users
  WHERE name ~* '^[sp][aeiou]';

As the operator is by definition case insensitive, you don’t need to worry about specifying both uppercase and lowercase letters in the character class.

These queries would give back a table with results similar to below:

name
Sergio
PAUL
samantha
Seraphina

Example query 2

As a second example, let’s say you want to find a hexadecimal color. You can use the POSIX class [:xdigit:] for this – it does the same as the character class [0-9a-fA-F].

Writing #[[:xdigit:]]{3} or #[[:xdigit:]]{6} would match a hexadecimal color in its shorthand or longhand form: the first one would match colors like #398 and the second one colors like #00F5C4.

You could combine them using character grouping and | to have one single RegEx pattern that matches both, and use it in the query as below:

SELECR color FROM styles
  WHERE color REGEXP '#([[:xdigit:]]{3}|[[:xdigit:]]{6})';
 

SELECR color FROM styles
  WHERE color ~ '#([[:xdigit:]]{3}|[[:xdigit:]]{6})';

This would give back something like below:

color
#341
#00fa67
#FF00AB

The POSIX class [:xdigit:] already includes both uppercase and lowercase letters, so you would not need to worry about if the operator is case sensitive or not.

Note on resource use

Depending on the size of your tables, a Contains String query can be really resource-intensive. Be careful when you’re using them in production databases, as you don’t want to have your app stop working.

Conclusion

The Contains String queries are really useful. You have learned how to use them in this article, and you’ve seen a few examples.

Hopefully you have added a new tool to your arsenal, and you enjoy using it! Just be careful not to crash your app.

---

---

Learn to code for free. freeCodeCamp’s open source curriculum has helped more than 40,000 people get jobs as developers. Get started

Синтаксис

• Wild Card с%: SELECT * FROM [table] WHERE [имя_столбца] Как «% Value%»

  Wild Card with _: SELECT * FROM [table] WHERE [column_name] Как ‘V_n%’

  Wild Card с [charlist]: SELECT * FROM [table] WHERE [column_name] Как ‘V [abc] n%’

замечания

Матч открытого шаблона

Подстановочный знак % добавленный к началу или концу (или обеим) строки, будет содержать 0 или более символов до начала или после окончания шаблона.

Использование «%» в середине позволит совместить 0 или более символов между двумя частями шаблона.

Мы собираемся использовать эту таблицу сотрудников:

Я бы	FName	LName	Номер телефона	ManagerID	DepartmentID	Оплата труда	Дата приема на работу
1	Джон	Джонсон	2468101214	1	1	400	23-03-2005
2	Софи	Amudsen	2479100211	1	1	400	11-01-2010
3	Ronny	кузнец	2462544026	2	1	600	06-08-2015
4	Джон	Sanchez	2454124602	1	1	400	23-03-2005
5	Хильде	сук	2468021911	2	1	800	01-01-2000

Следующие утверждения соответствуют всем записям, содержащим FName, содержащие строку «on» из таблицы Employees.

SELECT * FROM Employees WHERE FName LIKE '%on%';

Я бы	FName	LName	Номер телефона	ManagerID	DepartmentID	Оплата труда	Дата приема на работу
3	R на ny	кузнец	2462544026	2	1	600	06-08-2015
4	J on	Sanchez	2454124602	1	1	400	23-03-2005

Следующий оператор соответствует всем записям, имеющим PhoneNumber, начиная со строки «246» от Employees.

SELECT * FROM Employees WHERE PhoneNumber LIKE '246%';

Я бы	FName	LName	Номер телефона	ManagerID	DepartmentID	Оплата труда	Дата приема на работу
1	Джон	Джонсон	246 8101214	1	1	400	23-03-2005
3	Ronny	кузнец	246 2544026	2	1	600	06-08-2015
5	Хильде	сук	246 8021911	2	1	800	01-01-2000

Следующий оператор соответствует всем записям с номером PhoneNumber, заканчивающимся на строку «11» от Employees.

SELECT * FROM Employees WHERE PhoneNumber LIKE '%11'

Я бы	FName	LName	Номер телефона	ManagerID	DepartmentID	Оплата труда	Дата приема на работу
2	Софи	Amudsen	24791002 11	1	1	400	11-01-2010
5	Хильде	сук	24680219 11	2	1	800	01-01-2000

Все записи, где 3-й символ Fname — «n» от сотрудников.

SELECT * FROM Employees WHERE FName LIKE '__n%';

(два символа подчеркивания используются до «n», чтобы пропустить первые 2 символа)

Я бы	FName	LName	Номер телефона	ManagerID	DepartmentID	Оплата труда	Дата приема на работу
3	Ronny	кузнец	2462544026	2	1	600	06-08-2015
4	Джон	Sanchez	2454124602	1	1	400	23-03-2005

Совпадение одного символа

Чтобы расширить выбор оператора структурированного запроса (SQL-SELECT), можно использовать подстановочные знаки, знак процента (%) и подчеркивание (_).

Символ _ (подчеркивание) может использоваться в качестве подстановочного знака для любого отдельного символа в совпадении с шаблоном.

Найдите всех сотрудников, чье Fname начинается с «j» и заканчивается на «n» и имеет ровно 3 символа в Fname.

SELECT * FROM Employees WHERE FName LIKE 'j_n'

_ (подчеркивание) также может использоваться более одного раза в качестве дикой карты для соответствия шаблонам.

Например, этот шаблон будет соответствовать «jon», «jan», «jen» и т. Д.

Эти имена не будут отображаться «jn», «john», «jordan», «justin», «jason», «julian», «jillian», «joann», потому что в нашем запросе используется один знак подчеркивания, и он может пропустить точно один символ, поэтому результат должен иметь 3 символа Fname.

Например, этот шаблон будет соответствовать «LaSt», «LoSt», «HaLt» и т. Д.

SELECT * FROM Employees WHERE FName LIKE '_A_T'

Соответствие диапазону или набору

Сопоставьте любой отдельный символ в указанном диапазоне (например: [af] ) или установите (например: [abcdef] ).

Этот шаблон диапазона будет соответствовать «gary», но не «mary»:

SELECT * FROM Employees WHERE FName LIKE '[a-g]ary'

Этот шаблон будет соответствовать «mary», но не «gary»:

SELECT * FROM Employees WHERE Fname LIKE '[lmnop]ary'

Диапазон или набор можно также отменить, добавив ^ каретку перед диапазоном или установить:

Этот шаблон диапазона не будет соответствовать «gary», но будет соответствовать «mary»:

SELECT * FROM Employees WHERE FName LIKE '[^a-g]ary'

Этот шаблон набора не будет соответствовать «mary», но будет соответствовать «gary»:

SELECT * FROM Employees WHERE Fname LIKE '[^lmnop]ary'

Матч ЛЮБОЙ против ВСЕХ

Совпадение:
Необходимо совместить хотя бы одну строку. В этом примере тип продукта должен быть «электроникой», «книгами» или «видео».

SELECT *
FROM   purchase_table
WHERE  product_type LIKE ANY ('electronics', 'books', 'video');

Все совпадение (должно соответствовать всем требованиям).
В этом примере должны быть согласованы как «объединенное королевство», так и «лондон» и «восточная дорога» (включая вариации).

SELECT *
FROM   customer_table
WHERE  full_address LIKE ALL ('%united kingdom%', '%london%', '%eastern road%');

Отрицательный выбор:
Используйте ВСЕ, чтобы исключить все элементы.
В этом примере приводятся все результаты, когда тип продукта не является «электроникой», а не «книгами», а не «видео».

SELECT *
FROM   customer_table
WHERE  product_type NOT LIKE ALL ('electronics', 'books', 'video');

Поиск диапазона символов

Следующий оператор соответствует всем записям, имеющим FName, которое начинается с буквы от A до F из таблицы Employees .

SELECT * FROM Employees WHERE FName LIKE '[A-F]%'

Выписка ESCAPE в LIKE-запросе

Если вы реализуете текстовый поиск как LIKE -query, вы обычно делаете это так:

SELECT * 
FROM T_Whatever 
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%') 

Однако (помимо того, что вы не должны использовать LIKE когда вы можете использовать полнотекстовый поиск), это создает проблему, когда кто-то вводит текст типа «50%» или «a_b».

Таким образом (вместо перехода на полнотекстовый поиск) вы можете решить эту проблему с помощью инструкции LIKE -escape:

SELECT * 
FROM T_Whatever 
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%') ESCAPE ''

Это означает, что теперь будет рассматриваться как символ ESCAPE. Это означает, что теперь вы можете просто добавить к каждому символу в строке, которую вы ищете, и результаты будут корректными, даже если пользователь вводит специальный символ, например % или _ .

например

string stringToSearch = "abc_def 50%";
string newString = "";
foreach(char c in stringToSearch) 
     newString += @"" + c;
 
sqlCmd.Parameters.Add("@in_SearchText", newString); 
// instead of sqlCmd.Parameters.Add("@in_SearchText", stringToSearch);

Примечание. Вышеупомянутый алгоритм предназначен только для демонстрационных целей. Он не будет работать в случаях, когда 1 графема состоит из нескольких символов (utf-8). например string stringToSearch = "Les Miseu0301rables"; Вам нужно будет сделать это для каждой графемы, а не для каждого персонажа. Вы не должны использовать вышеуказанный алгоритм, если имеете дело с азиатскими / восточно-азиатскими / южноазиатскими языками. Вернее, если вам нужен правильный код для начала, вы должны просто сделать это для каждого graphemeCluster.

См. Также ReverseString, вопрос интервью с C #

Подстановочные знаки

подстановочные символы используются с оператором SQL LIKE. Подстановочные знаки SQL используются для поиска данных в таблице.

Подстановочные знаки в SQL:%, _, [charlist], [^ charlist]

% — заменить ноль или более символов

   Eg:  //selects all customers with a City starting with "Lo"
        SELECT * FROM Customers
        WHERE City LIKE 'Lo%';

       //selects all customers with a City containing the pattern "es"
      SELECT * FROM Customers
       WHERE City LIKE '%es%';

_ — заменить один символ

Eg://selects all customers with a City starting with any character, followed by "erlin"
SELECT * FROM Customers
WHERE City LIKE '_erlin';

[charlist] — устанавливает и диапазоны символов для соответствия

Eg://selects all customers with a City starting with "a", "d", or "l"
SELECT * FROM Customers
WHERE City LIKE '[adl]%';

//selects all customers with a City starting with "a", "d", or "l"
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

[^ charlist] — Соответствует только символу, не указанному в скобках

Eg://selects all customers with a City starting with a character that is not "a", "p", or "l"
SELECT * FROM Customers
WHERE City LIKE '[^apl]%';

or

SELECT * FROM Customers
WHERE City NOT LIKE '[apl]%' and city like '_%';