CodeRang
SQL topics

SQL Regex: Regular Expressions and REGEXP Practice

SQL regex lets you match structured text that is difficult to express with LIKE. This example uses PostgreSQL regular expressions to recognize a code inside a longer message and extract the matching fragment.

Updated · SQL Arena team

Regex courseAll problems

What you will practice

  • Filter text with PostgreSQL ~ and ~*
  • Extract matches with regexp_match
  • Clean text with regexp_replace
  • Use anchors, quantifiers, and character classes
  • Choose between LIKE, SIMILAR TO, and regular expressions

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Extract an order code from a support message

Support messages may contain an order code made of two Latin letters, a hyphen, and four digits, such as AB-4821. Letter case may vary. Return only messages containing a valid code and output ticket_id, order_code, and agent_name. Sort the result by ticket id.

Query 1PostgreSQLv16
SELECT
  t.id AS ticket_id,
  (regexp_match(t.message, '[A-Z]{2}-[0-9]{4}', 'i'))[1] AS order_code,
  a.name AS agent_name
FROM tickets t
JOIN agents a ON a.id = t.agent_id
WHERE t.message ~* '[A-Z]{2}-[0-9]{4}'
ORDER BY t.id;
Submit
Resultagentstickets
Answer accepted

How SQL Regex Matching Works

The pattern [A-Z]{2}-[0-9]{4} describes the code rather than one specific value. [A-Z] matches a letter in the selected range, {2} requires exactly two of them, the hyphen is literal, and [0-9]{4} requires four digits. There are no ^ or $ anchors because the order code appears inside a longer support message.

The WHERE clause uses ~*, PostgreSQL's case-insensitive regular-expression operator. That means both AB-4821 and xy-1044 are accepted. If the business rule required uppercase codes only, ~ would be more appropriate because it is case-sensitive. Choosing between the two operators is part of the data rule, not merely a syntax preference.

regexp_match extracts the matching text after the row has passed the filter. The 'i' flag makes the function case-insensitive as well. Because regexp_match returns an array, [1] selects its first element. The join provides the agent name, while ORDER BY ticket id guarantees a predictable result.

A regular expression describes a text pattern rather than one exact string. Character classes such as [A-Z] and [0-9] match letters or digits, while quantifiers such as {2} specify how many times an element must appear. Anchors ^ and $ restrict matching to the beginning and end of a string. PostgreSQL uses ~ for case-sensitive regex matching and ~* for case-insensitive matching. Functions such as regexp_match can return the text captured by the expression instead of only testing whether a match exists.

SQLPostgreSQL v16
SELECT
  t.id AS ticket_id,
  (regexp_match(t.message, '[A-Z]{2}-[0-9]{4}', 'i'))[1] AS order_code,
  a.name AS agent_name
FROM tickets t
JOIN agents a ON a.id = t.agent_id
WHERE t.message ~* '[A-Z]{2}-[0-9]{4}'
ORDER BY t.id;
Query result
ticket_idorder_codeagent_name
201AB-4821Nina
202xy-1044Ethan
203CD-7730Mila
205EF-5502Liam
207GH-1208Mila

Common SQL Regex and REGEXP Mistakes

01

Stretching LIKE beyond its purpose

LIKE is good for simple prefix, suffix, and substring patterns using % and _. It cannot naturally express a strict rule such as exactly two letters followed by exactly four digits. A regular expression is clearer when the shape of the text matters.

02

Anchoring a pattern that should match inside text

^ and $ mean the beginning and end of the whole string. A pattern such as ^[A-Z]{2}-[0-9]{4}$ validates a column that contains only the code, but it will fail when the same code appears inside a sentence.

03

Ignoring case sensitivity

~ is case-sensitive and ~* is case-insensitive. Regex functions also support flags that affect case handling. The wrong choice can silently exclude valid rows, which makes this bug easy to miss if your sample data contains only one capitalization style.

Practice problems on this topic

Regex course

Once you can detect one structured code, practice different text problems instead of repeating the same pattern. SQL Arena includes tasks where you exclude a word from city names, count order codes, extract the first code, and identify error statuses in log text. More advanced exercises use regex for transformation and cleanup: extracting all order codes, reading floor area hidden in listing text, cleaning support tickets, or removing HTML litter from product descriptions. The “Regular Expressions” course provides a structured route through these techniques, with a query task in each lesson.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL regex?

SQL regex means using regular expressions in SQL to match or process text according to a pattern. Syntax differs by database, so PostgreSQL, MySQL, and other systems expose different regex operators and functions.

How do you use regex in SQL?

You provide a regular expression to the operator or function supported by your database. In PostgreSQL, ~ tests a case-sensitive match and ~* performs the same test without case sensitivity.

What is the difference between SQL LIKE and regex?

LIKE provides two basic wildcards, % and _, for simple pattern matching. Regex supports character classes, repetition, anchors, alternatives, and other rules for more structured text.

How do you use REGEXP in PostgreSQL?

PostgreSQL does not rely on a single REGEXP operator. It provides ~ and ~* for matching plus functions such as regexp_match, regexp_replace, and regexp_split_to_array for additional regex operations.

How do you extract text with SQL regex?

Use a regex extraction function supported by the database. In PostgreSQL, regexp_match can return the part of the string matched by the expression or its capturing groups.

See also

Practice SQL Regex Queries

SQL Arena has 1,192 automatically checked tasks, so regex exercises can use different table structures and text formats instead of one reusable sample. Solutions are also evaluated on hidden data. The free plan includes 292 tasks across PostgreSQL, MySQL, ClickHouse, and BigQuery with no card required. For a guided sequence from basic matching to extraction and cleanup, continue with the “Regular Expressions” course.

Practice SQL Regex

More practice from CodeRang