6 chapters are open
First lesson: Операторы ~ и ~*: где LIKE сдаётся
Interactive course · regular expressions
LIKE looks for letters.
A regex looks for patterns.
Phones in ten formats, order codes inside emails, a server log, dates with dots and with slashes. The course teaches regular expressions where such text lives — right inside PostgreSQL queries: find, validate, extract and replace. With Cyrillic text and the differences from Python and JavaScript.
- Every match is visible: a marker brackets it
- Exercises are graded by the query result
- The first chapter is free — 3 lessons
- Zero installation: all in the browser
- Заказ ⟦KM-2024-001234⟧ до сих пор не пришёл
- Верните деньги за заказ ⟦km-2024-000777⟧
- Заказы ⟦KM-2024-003001⟧, ⟦KM-2024-003002⟧ и ⟦KM-2024-003015⟧
What hurts
A task from work — and the technique that closes it
On the left, what people arrive with. On the right, the technique from the program and what the chapter covers.
LIKE finds the word inside every other word
a pattern instead of lettersThe ~ and ~* operators, character sets and word boundaries: find a word rather than a fragment, and keep Cyrillic letters intact.
Phones in the export are written ten different ways
cleaning and validationStrip the noise, check there are exactly eleven digits, rebuild one format — without losing NULL rows.
An order code hides inside free text
capture groupssubstring, regexp_substr and regexp_matches pull the code into its own column, even when one message holds three.
The pattern swallowed half the string
greedGreedy and lazy quantifiers, an exact set instead of “dot-star”, and a PostgreSQL rule textbooks skip.
A regex copied from Python silently finds nothing in the database
dialectsHow PostgreSQL differs from Python, JavaScript and RE2 in ClickHouse and BigQuery: \b and \y, \w and Cyrillic, named groups, lookaround.
This course is for you if
- Analysts and data engineers who clean text inside the database: phones, emails, codes, logs
- Anyone who copies regexes from Stack Overflow and cannot explain why they work
- Anyone who knows SQL basics and wants to stop chaining ten LIKE conditions
- Anyone who works with Cyrillic text and is tired of surprises with case and special letters
And NOT for you if
- Anyone who needs regexes only in JavaScript or Java: the course runs in PostgreSQL and covers other dialects by comparison
- Anyone who has never written a SELECT: SQL basics are not taught here
- Anyone after automata theory: the course is practical, with just enough theory to debug patterns
How it works
Pattern — run — marker
Every lesson has live cells, a trap quiz and a fix-the-query exercise. A pattern runs on real data at once, and a marker shows exactly what matched.
A match marker
From lesson one you have a marker: a query brackets every match with ⟦ ⟧. You see where a pattern fired, where it took too much and where it stayed silent.
Messy data, not synthetic examples
A contact export, support tickets and the web server log of Kotomarket: typos, double spaces, Cyrillic letters, codes in mixed case and one broken line.
Graded by the result
In a fix-the-query exercise your SQL runs in the sandbox and is compared with the reference. Any pattern that finds the same thing passes.
The eighth shift of one story
The tail of the first signal
The first signal brought Vault-9 a snapshot of Kotomarket — and a tail the station’s decoder could not lay out into tables: a web server log, support tickets, a contact export. For a century and a half the tail lay in the Sorting Room, the dead end of the intake level where the station dumps everything it failed to understand.
The commandant hands it to you. The mentor is the same — QUERY, the old archive intelligence with the temper of a cat — and the language is new: the language of patterns. Chapter by chapter the tail turns into clean records, and the last evening of the server log holds what was worth reading to the end: how the store’s database reached the station.
The program
6 chapters, 18 lessons, 9 h 35 min
Open chapters show their lessons and your progress. The first chapter is free; the rest are part of the Pro plan.
Basics: pattern search instead of LIKE
The signal brought raw text, and LIKE drowns in it. The chapter teaches you to search for patterns instead of letters: the ~ and ~* operators, special characters and escaping, character sets — and the trap with the Russian letter «ё» that nearly everyone falls into.
Quantifiers: how many times to repeat
Quantifiers + * ? and braces: an order code, a postcode and a time without spelling out every digit. Then greed: a pattern that swallows a whole message, and PostgreSQL’s own rule about mixed greediness.
Anchors and boundaries: checking the whole string
Finding a piece and validating a whole string are different jobs. Anchors ^ and $, word boundaries \y, \m, \M (and why \b stays silent in PostgreSQL), reasonably strict email and phone validators — without losing NULLs.
Groups: extracting and replacing part of the text
Alternation and brackets, capture groups, substring, regexp_substr and regexp_matches — pull an order code into its own column. Replacement with \1 and \&: ISO dates, one spelling per city, phones in a single format.
Backreferences, lookaround and flags
Backreferences catch repeats, lookahead and lookbehind mask a card number and check a password, flags change the search itself, and regexp_split_to_table cuts text into words.
Practice: logs, other people's patterns, dialects
The nginx log of Kotomarket’s last evening is split into columns by a single regex. Then someone else’s pattern, read piece by piece and in (?x) mode, and how regexes behave in Python, JavaScript, ClickHouse and BigQuery — catastrophic backtracking included.
The honest boundary
What is not in the course, and why
Parsing HTML, JSON and URLs with regexes
These formats have structure, and a regex breaks on the first nested tag or escaped quote.
The course shows where to stop: JSON operators, split_part and plain SQL — in the lesson on other people’s regexes.
The whole of PCRE
Atomic groups, conditionals and control verbs exist in some engines only, and PostgreSQL has none of them.
The course teaches the core that works everywhere, and separately how dialects differ, so patterns travel without surprises.
FAQ
Frequently asked
The basics are enough: SELECT, WHERE and ORDER BY. The regexes in the course run inside PostgreSQL queries, and every new SQL trick — a function, or a function in FROM — is explained where it appears.
The tail of the signal is waiting for its sorter
The first chapter is open in full — 3 lessons, no subscription and no card.