Підготовка даних за допомогою Regex в SQL - javascript.org.ua

kata kunci : REGEXP, REGEXPLIKE, REGEXPREPLACE

Regex SQL — це можливість, якою володіють деякі системи управління базами даних SQL для використання регулярних виразів (regex) під час пошуку, фільтрації та маніпулювання текстовими даними.

Ілюстрація:

У цьому випадку, провінція, про яку йдеться, насправді є однаковою — це «Йог’якарта», але через формат вільного тексту користувачі можуть вільно вводити назву своєї провінції.

Для потреби аналізу даних, наприклад, агрегації, це може призвести до різних результатів аналізу, тому дані повинні бути стандартизовані або приведені до єдиного формату. Наприклад, використовуючи методи case та when :

Пояснення:

Інші символи для Regex:

Приклад 1: Припустимо, потрібно змінити «1kroma» на «kroma-1», для цього можна використати REGEXP_REPLACE("1kroma", "([0-9])([a-z]+)", "$2-$1"), таким чином отримаємо новий текст — «kroma-1», оскільки ми змінили місцями групу 2 ($2) на початку, потім додали дефіс ( - ) і на кінець поставили групу 1 ($1).

Приклад 2 (Витягнути два середні літери): Ось приклад, коли мені потрібно відфільтрувати дані та показати всіх записів, де ім’я співробітника в колонці staf_pencatat в таблиці dqlabregex має бути «Senja» або «Sendja». Однак потрібно точно вибрати правильну нотацію для regex, щоб вона збігалася не тільки з текстами «Senja», «Sendja», але й з такими варіантами, як «SenDja», «Sen_ja» тощо.

SELECT * FROM dqlabregex WHERE staf_pencatat REGEXP 'Sen.?ja';

+---------------+---------------+----------+---------------+---------------+  
| no_pencatatan | tanggal_catat | kota | jumlah_member | staf_pencatat |  
+---------------+---------------+----------+---------------+---------------+  
| 5 | 05/10/2020 | Semarang | 8 | Senja |  
| 6 | 06/28/2020 | Semarang | 12 | Sendja |  
+---------------+---------------+----------+---------------+---------------+

Приклад 3 (Наявність літер у числовому форматі): У колонці jumlah_member таблиці dqlabregex вказується кількість, тобто тільки числові (номінальні) значення повинні бути вмістом цього стовпця (record data). Однак, через помилки введення даних, в цій колонці є нечислові символи.

Перевірте та виведіть записи з помилками введення даних у колонці? Порада: створіть запит, який покаже всі записи з неправильними числовими значеннями в колонці jumlah_member.

SELECT * FROM dqlabregex WHERE jumlah_member REGEXP '^[0-9]';

+---------------+---------------+----------+---------------+---------------+  
| no_pencatatan | tanggal_catat | kota | jumlah_member | staf_pencatat |  
+---------------+---------------+----------+---------------+---------------+  
| 1 | 01-05-2020 | Jakarta | 1311 | Andra |  
| 2 | 30-06-2020 | Jakarta | 12O5 | Andra |  
| 3 | 05/02/2020 | Bandung | 5O0 | Antara |  
| 4 | 06/28/2020 | Bandung | 67O | Antara |  
| 5 | 05/10/2020 | Semarang | 8 | Senja |  
| 6 | 06/28/2020 | Semarang | 12 | Sendja |  
+---------------+---------------+----------+---------------+---------------+

REGEXP LIKE

REGEXP_LIKE — це запит у SQL, функція якого подібна до запиту LIKE (запит, який використовується в клаузі WHERE (умови) для пошуку певних шаблонів у колонці). Але в REGEXP_LIKE підтримуються вирази regex і є опція (option), яка дозволяє змінити поведінку стандартного порівняння цієї функції.
Мова йде про те, що якщо regex за замовчуванням є чутливим до регістру (case sensitive), то за допомогою REGEXP_LIKE можна ініціалізувати параметр для зміни цього, щоб ігнорувати чутливість до регістру (ignore case sensitive) або, навпаки, зберегти чутливість до регістру (keep case sensitive).

Загальна форма синтаксису для REGEXP_LIKE виглядає наступним чином:

SELECT * FROM nama_tabel WHERE REGEXP_LIKE (nama_kolom, 'argumen', match_parameter)

Пояснення:

'argumen' — це комбінація бажаних регулярних виразів. match_parameter — це літерал тексту, який дозволяє змінювати стандартну поведінку функції порівняння.

Типи параметрів, які можна використовувати для match_parameter:

Приклад 1 (Пошук на початку): пошук імен співробітників staf_pencatat в таблиці, які починаються на 'an' з ігноруванням чутливості до регістру.

SELECT * FROM dqlabregex WHERE REGEXP_LIKE(staf_pencatat,'^AN')

+---------------+---------------+---------+---------------+---------------+  
| no_pencatatan | tanggal_catat | kota | jumlah_member | staf_pencatat |  
+---------------+---------------+---------+---------------+---------------+  
| 1 | 01-05-2020 | Jakarta | 1311 | Andra |  
| 2 | 30-06-2020 | Jakarta | 12O5 | Andra |  
| 3 | 05/02/2020 | Bandung | 5O0 | Antara |  
| 4 | 06/28/2020 | Bandung | 67O | Antara |  
+---------------+---------------+---------+---------------+---------------+

Приклад 2: показати всі дані з фільтруванням імен співробітників на колонці staf_pencatat в таблиці dqlabregex, де ім’я співробітника — SenDja або Sen_ja. Однак цього разу ігноруємо чутливість до регістру.

SELECT * FROM dqlabregex WHERE REGEXP_LIKE (staf_pencatat, 'sen.?ja', 'i')

+---------------+---------------+----------+---------------+---------------+  
| no_pencatatan | tanggal_catat | kota | jumlah_member | staf_pencatat |  
+---------------+---------------+----------+---------------+---------------+  
| 5 | 05/10/2020 | Semarang | 8 | Senja |  
| 6 | 06/28/2020 | Semarang | 12 | Sendja |  
+---------------+---------------+----------+---------------+---------------+

Приклад 3: Андра попросив мене перевірити і показати помилки введення даних у колонці jumlah_member. На щастя, Андра також дав мені пораду створити запит, який покаже всі дані з помилками введення чисел у колонці jumlah_member, і при цьому ігнорує чутливість до регістру.

SELECT * FROM dqlabregex WHERE REGEXP_LIKE (jumlah_member,'[^0-9]','i');

+---------------+---------------+---------+---------------+---------------+  
| no_pencatatan | tanggal_catat | kota | jumlah_member | staf_pencatat |  
+---------------+---------------+---------+---------------+---------------+  
| 2 | 30-06-2020 | Jakarta | 12O5 | Andra |  
| 3 | 05/02/2020 | Bandung | 5O0 | Antara |  
| 4 | 06/28/2020 | Bandung | 67O | Antara |  
+---------------+---------------+---------+---------------+---------------+

REGEXP REPLACE

REGEXP_REPLACE — це запит у SQL, функція якого схожа на запит REPLACE (запит, що використовується для заміни певного рядка/символу в колонці на інший рядок/символ), але в REGEXP_REPLACE підтримуються регулярні вирази.

Загальна форма синтаксису для REGEXP_REPLACE виглядає наступним чином:

SELECT REGEXP_REPLACE (nama_kolom, 'string yang ingin diganti', 'string pengganti') FROM nama_tabel

Рядок, який потрібно замінити в обраній колонці, підтримує регулярні вирази, якщо використовується запит REGEXP_REPLACE.

Приклад 1: Замінити текст 'Sendja' на 'Senja' в колонці staf_pencatat. Використовуємо регулярний вираз, щоб у майбутньому, якщо з’явиться схожий текст, наприклад, 'Sen_ja', 'Sen ja' тощо, його було перетворено на єдиний текст — 'Senja'.
Після цього змініть назву колонки на pencatat за допомогою запиту з використанням ALIAS (AS).

SELECT REGEXP_REPLACE(staf_pencatat, 'Sen.?ja','Senja') AS pencatat  
FROM dqlabregex

+----------+  
| pencatat |  
+----------+  
| Andra |  
| Andra |  
| Antara |  
| Antara |  
| Senja |  
| Senja |  
+----------+

Приклад 2: У таблиці dqlabregex є колонка jumlah_member, яка містить нечислові символи (не цифри) в record-ах. Чи можеш ти видалити нечислові символи з цієї колонки? Показати всі колонки таблиці без зміни їхніх назв та порядку, але за умови, що нечислові символи будуть видалені з колонки jumlah_member.

SELECT no_pencatatan, tanggal_catat, kota, REGEXP_REPLACE(jumlah_member, '[^0-9]', '') AS jumlah_member, staf_pencatat  
FROM dqlabregex

+---------------+---------------+----------+---------------+---------------+  
| no_pencatatan | tanggal_catat | kota | jumlah_member | staf_pencatat |  
+---------------+---------------+----------+---------------+---------------+  
| 1 | 01-05-2020 | Jakarta | 1311 | Andra |  
| 2 | 30-06-2020 | Jakarta | 125 | Andra |  
| 3 | 05/02/2020 | Bandung | 50 | Antara |  
| 4 | 06/28/2020 | Bandung | 67 | Antara |  
| 5 | 05/10/2020 | Semarang | 8 | Senja |  
| 6 | 06/28/2020 | Semarang | 12 | Sendja |  
+---------------+---------------+----------+---------------+---------------+

Приклад 3: У таблиці dqlabregex колонка tanggal_catat має різний формат дати на перших двох рядках порівняно з іншими. Щоб привести дані до єдиного формату, потрібно змінити формат запису дати з DD-MM-YYYY (день-місяць- рік) на MM/DD/YYYY (місяць/день/рік). Приведіть формат дати в колонці до єдиного вигляду та змініть назву колонки на tanggal_pencatatan.
Показати початкову колонку (tanggal_catat) та колонку після стандартизації (tanggal_pencatatan).

SELECT tanggal_catat, REGEXP_REPLACE(tanggal_catat,'([0-9]{2})-([0-9]{2})-([0-9]{4})', '$2/$1/$3') AS tanggal_pencatatan  
FROM dqlabregex

+---------------+--------------------+  
| tanggal_catat | tanggal_pencatatan |  
+---------------+--------------------+  
| 01-05-2020 | 05/01/2020 |  
| 30-06-2020 | 06/30/2020 |  
| 05/02/2020 | 05/02/2020 |  
| 06/28/2020 | 06/28/2020 |  
| 05/10/2020 | 05/10/2020 |  
| 06/28/2020 | 06/28/2020 |  
+---------------+--------------------+

ПРАКТИКА

Практика 1: Виконайте зміни record-ів у кожній колонці таблиці dqlabregex. Ось кілька важливих зауважень, які мені надав Антара:

У колонці tanggal_catat змініть всі формати дат на формат, що підтримується SQL, один із яких - формат YYYY-MM-DD.
Видаліть усі нечислові символи з колонки jumlah_member.
Замініть record, що містять Sendja або її варіанти, на Senja.
Назви колонок та їхній порядок не змінюються.

SELECT no_pencatatan,  
CASE  
 WHEN REGEXP_LIKE(tanggal_catat, '([0-9]{2})-([0-9]{2})-([0-9]{4})')  
 THEN REGEXP_REPLACE(tanggal_catat, '([0-9]{2})-([0-9]{2})-([0-9]{4})', '$3-$2-$1')  
 ELSE  
 REGEXP_REPLACE(tanggal_catat, '([0-9]{2})/([0-9]{2})/([0-9]{4})', '$3-$1-$2')  
END AS tanggal_catat,  
kota,  
REGEXP_REPLACE(jumlah_member, '[^0-9]', '') AS jumlah_member,  
REGEXP_REPLACE(staf_pencatat, 'Sen.?ja','Senja') AS staf_pencatat  
FROM dqlabregex;

+---------------+---------------+----------+---------------+---------------+  
| no_pencatatan | tanggal_catat | kota | jumlah_member | staf_pencatat |  
+---------------+---------------+----------+---------------+---------------+  
| 1 | 2020-05-01 | Jakarta | 1311 | Andra |  
| 2 | 2020-06-30 | Jakarta | 125 | Andra |  
| 3 | 2020-05-02 | Bandung | 50 | Antara |  
| 4 | 2020-06-28 | Bandung | 67 | Antara |  
| 5 | 2020-05-10 | Semarang | 8 | Senja |  
| 6 | 2020-06-28 | Semarang | 12 | Senja |  
+---------------+---------------+----------+---------------+---------------+

Практика 2: Загальна кількість учасників, яких зареєстрував кожен staf_pencatat, це сума значень з колонки jumlah_member, згрупованих за staf_pencatat, а потім відсортованих від найменших до найбільших. Не забувайте перед виконанням очистити дані: видалити нечислові символи з колонки jumlah_member та замінити текст 'Sendja' на 'Senja' за допомогою регулярних виразів.

SELECT  
 SUM(REGEXP_REPLACE(jumlah_member, '[^0-9]', '')) AS total_member,  
 REGEXP_REPLACE(staf_pencatat, 'Sen.?ja', 'Senja') AS staf_pencatat  
FROM dqlabregex  
GROUP BY 2 ORDER BY 1;

+--------------+---------------+  
| total_member | staf_pencatat |  
+--------------+---------------+  
| 20 | Senja |  
| 117 | Antara |  
| 1436 | Andra |  
+--------------+---------------+  




Перекладено з: [Preparation Data dengan Regex SQL](https://yeftawidianto.medium.com/preparation-data-dengan-regex-sql-d8e8c3a06a0e)

REGEXP LIKE

REGEXP REPLACE

ПРАКТИКА

Вам також може сподобатися

Leave a Reply