SQL REGEXP_REPLACE

REPLACE's more powerful sibling — matches a PATTERN instead of a fixed string, using regular expressions.

What & Why

REGEXP_REPLACE(text, pattern, replacement, flags) replaces text matching a regular expression pattern, rather than an exact literal string. This is the tool for problems REPLACE genuinely can't express — like "collapse any number of consecutive spaces into one," which has no single fixed substring to search for.

See How It Works

BUSINESS QUESTION

Marketing wants campaign names converted into lowercase hyphenated slugs.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

EXAMPLE QUERY

SELECT
  id AS campaign_id,
  name,
  LOWER(REGEXP_REPLACE(TRIM(name), '\s+', '-', 'g')) AS campaign_slug
FROM marketing.campaigns
ORDER BY campaign_id;

RESULT — exact output from the displayed Queryflo rows

campaign_id	name	campaign_slug
1	Spring Launch	spring-launch
2	Retention Webinar	retention-webinar
3	Finance Retargeting	finance-retargeting
4	Enterprise Search	enterprise-search

The regex replaces each real name's whitespace with a hyphen before LOWER.

Now You Try

Practice this concept

Marketing wants campaign names converted into lowercase slugs with every whitespace run replaced by one hyphen.

Available schema

marketing

Prefix tables with marketing.table_name.

campaign_idnamecampaign_slug

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

query.sql

Intermediate business practice