Week 115 – Intermediate

We all have tools at our disposal that are very powerful but go unused for a long time. A tool that once we stop using it, we start losing our ability to understand how it worked.

The tool somewhere near the top of our list that follows that reference is Regular Expressions (or RegEx for short).
To give everyone the opportunity to stretch those muscles again , we’ve got 4 RegEx challenge for you to tackle!

Challenge 1: Validate Complex Email Addresses

Objective: Create a query that uses REGEXP_LIKE to validate complex email addresses.

Pattern Requirements:

• Must begin with alphanumeric characters (a-z, A-Z, 0-9).
• May include dots (.) or hyphens (-), but these cannot be at the start or end.
• The domain should have two parts separated by a dot: the domain name and a valid top-level domain (TLD).
• TLD must be 2 to 6 letters long.

CREATE TABLE users (
user_id INT AUTOINCREMENT ,
email VARCHAR
);

INSERT INTO users (email) VALUES
('john.doe@example.com'),
('invalid-email@.com'),
('alice_smith@sub-domain.co.uk'),
('bob@domain.com'),
('user@invalid_domain@com');

Challenge 2: Extract Valid Dates in Multiple Formats

Objective: Use REGEXP_SUBSTR to extract dates in different formats from a string column, assuming the dates follow one of these formats:

• DD/MM/YYYY
• MM-DD-YYYY
• YYYY.MM.DD

CREATE TABLE documents (
doc_id INT AUTOINCREMENT PRIMARY KEY,
text_column VARCHAR(500)
);

INSERT INTO documents (text_column) VALUES
('This document was created on 15/03/2023.'),
('The report is due by 04-15-2022.'),
('Version 1.0 released on 2021.08.30.'),
('No date provided in this text.'),
('Invalid date 32/13/2020.');

Challenge 3: Mask Credit Card Numbers

Objective: Use REGEXP_REPLACE to mask all but the last 4 digits of credit card numbers in a string. The credit card numbers can be in the format XXXX-XXXX-XXXX-XXXX or XXXXXXXXXXXX.

CREATE TABLE transactions (
transaction_id INT AUTOINCREMENT PRIMARY KEY,
card_number VARCHAR(50)
);
INSERT INTO transactions (card_number) VALUES
('1234-5678-9012-3456'),
('9876 5432 1098 7654'),
('1111222233334444'),
('4444-3333-2222-1111'),
('Invalid number 12345678901234567');

Challenge 4: Extract Hashtags from a Text Block

Objective: Use RegEx to extract all hashtags from a string, where hashtags:

• Begin with #
• Are followed by alphanumeric characters or underscores but no spaces.

CREATE TABLE social_posts (
post_id INT AUTOINCREMENT PRIMARY KEY,
text_column VARCHAR(500)
);
INSERT INTO social_posts (text_column) VALUES
('Check out our new product! #launch #excited'),
('Loving the weather today! #sunnyDay #relax'),
('Follow us at #example_page for more updates!'),
('No hashtags in this sentence.'),