SQL REPLACE

Swaps every occurrence of one substring for another — a straightforward find-and-replace, applied to every match in the string at once.

What & Why

REPLACE(text, from_str, to_str) replaces every occurrence of from_str with to_str. Every match gets replaced, not just the first one.

See How It Works

BUSINESS QUESTION

Marketing wants campaign names shown both with underscores and with spaces deleted so the two REPLACE behaviors are visible.

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

Watch REPLACE substitute and delete exact textStep 1 of 2

REPLACE(LOWER(name), ' ', '_')

id	name	campaign_key
1	Spring Launch	spring_launch
2	Retention Webinar	retention_webinar
3	Finance Retargeting	finance_retargeting
4	Enterprise Search	enterprise_search

Every exact space is replaced with an underscore in the export key.

EXAMPLE QUERY

SELECT
  id,
  name,
  REPLACE(LOWER(name), ' ', '_') AS campaign_key,
  REPLACE(LOWER(name), ' ', '') AS compact_key
FROM marketing.campaigns
ORDER BY id;

Now You Try

Practice this concept

Marketing wants campaign-name spaces both replaced and deleted for two export keys.

Available schema

marketing

Prefix tables with marketing.table_name.

idnamecampaign_keycompact_key

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