Практическая работа 10.
Регулярные выражения
Язык SQL поддерживает работу с регулярными выражениями (regular
expressions или RegEx). Это специальный язык, позволяющий достаточно точно
определить шаблоны поиска в строке.
[Link]
[Link]
При поиска текста регулярные выражения чувствительны к регистру,
специальные символы нужно выделить « \ ».
Примеры:
almaty
Almaty almaty Kazakhstan
Поиск одного символа:
- . - один любой символ
- [ab] - только a или b
- [a-z] - любая lower буква
- [A-Z] - любая upper буква
- [e-f] - строчная от e до f
- [0-9] - любая одна цифра
- [5-7] - любое число от 5 до 7
Примеры:
.txt
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
Поиск и исключение:
- [^ab] - не a или b
- [^a-z] - не lower буква
- [^A-Z] - не upper буква
- [^c-f] - любой символ но не от c до f
- [^0-9] - не цифра
- [^3-7] - число не между 3 до 7
Комбинации:
- [a-zA-Z] - любые буквы upper и lower
- [a-zA-Z0-9] - любые буквы upper и lower и цифры.
Поиск нескольких символов:
- * - 0 или более символов
- + - 1 или более символов
- ? - 0 или 1 символ
Примеры:
[a-zA-Z0-9]*
Метасимволы:
- \d = [0-9] = Цифровой символ
- \D = [^0-9] = Нецифровой символ
- \s = [ \f\n\r\t\v] = Пробельный символ
- \S = [^ \f\n\r\t\v] = Непробельный символ
- \w = Буквенный или цифровой символ или знак подчёркивания
- \W = Любой символ, кроме буквенного или цифрового символа или знака
подчёркивания
Примеры:
abc
Almaty
Caspian University
student123
25th of December
Пробельные метасимволы \s:
- \r - Возврат каретки (Carriage return, CR)
- \n - Перевод строки (Line feed, LF)
- \t - Табуляция (Tab)
- \v - Вертикальная табуляция (vertical tab)
- \f - Конец страницы (Form feed)
Пробельные метасимволы \s:
- ^ - начало строки (текста)
- $ - конец строки (текста)
- \b - граница слова
- \B - не граница слова
[a-z]\B
[a-zA-Z]+\b
\w+\@\w+\.\w+
email from test1@[Link] received
email sent to test2@[Link]
test email sent to test3@[Link]
email error to test4@[Link]
Функции регулярных выражений:
- REGEXP_CONTAINS(value, regexp) - TRUE/FALSE содержит или нет
- REGEXP_EXTRACT(value, regexp[, position[, occurrence]]) - возвращает
регулярное выражение
- REGEXP_INSTR(source_value, regexp [, position[, occurrence,
[occurrence_position]]]) - возвращает позицию регулярного выражения
- REGEXP_REPLACE(value, regexp, replacement) - заменяет регулярное
выражение.
REGEXP_CONTAINS(email, r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+')
REGEXP_EXTRACT(email, r'^[a-zA-Z0-9_.+-]+')
REGEXP_INSTR(email, '@[^.]*')
REGEXP_REPLACE(email, r'@\w+\.\w+', '')
Пример:
SELECT
email,
REGEXP_CONTAINS(email, r'@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+'),
REGEXP_EXTRACT(email, r'^[a-zA-Z0-9_.+-]+'),
REGEXP_INSTR(email, '@[^.]*'),
REGEXP_REPLACE(email, r'@\w+\.\w+', '')
FROM `my-project-1-387516.practice_9.regexp`
Задание:
1. Создайте новый Dataset для работы
2. Загрузите таблицы в формате .csv на компьютер, далее импортировать в
Big Query, все таблицы этой практической работы в новый датасет.
3. Содержит ли email только электронные почты, используйте регулярные
выражения? Если нет, покажите количество пользователей, в которых не указаны
электронные адреса.
4. Верните всех full_name, в имя начинается на «C» и фамилия на «E»,
используя регулярные выражения.
5. Верните last_name, которые состоят из четырех букв, используя
регулярные выражения.
6. Замените full_name на инициалы. Например: M. Deeneshwar
7. В столбце email вытащите имена после знака @.
8. В столбце email на какой позиции находиться знак @.
9. В столбце cmp_web_urls оставьте http:// и уберите только название
сайтов.
10. Приведите примеры использования метасимволов используя таблицу.