How does the SQL version of SUBSTITUTE work?

REPLACE(sku, '-', '') swaps every dash for nothing, like SUBSTITUTE with an empty replacement. REGEXP_REPLACE with a character class removes anything that is not a letter or digit, which SUBSTITUTE cannot do in one step. The 'g' flag means every match, not just the first.

Why can SQL give a different result from SUBSTITUTE?

SUBSTITUTE's optional instance number (replace only the second match) has no direct SQL equivalent.

SUBSTITUTE in SQL

SUBSTITUTE replaces one piece of text with another inside a value; SQL does this with REPLACE, or REGEXP_REPLACE for patterns.

The Excel version

=SUBSTITUTE(A2, "-", "")

The SQL equivalent

This example assumes: products(id bigint, sku text, name text). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       sku,
       REPLACE(sku, '-', '') AS sku_no_dashes,
       REGEXP_REPLACE(sku, '[^A-Za-z0-9]', '', 'g') AS sku_alphanumeric
FROM products
ORDER BY id;

How it works

REPLACE(sku, '-', '') swaps every dash for nothing, like SUBSTITUTE with an empty replacement.
REGEXP_REPLACE with a character class removes anything that is not a letter or digit, which SUBSTITUTE cannot do in one step.
The 'g' flag means every match, not just the first.

Where the results differ from Excel

SUBSTITUTE's optional instance number (replace only the second match) has no direct SQL equivalent.
SUBSTITUTE is case-sensitive, as is REPLACE; use REGEXP_REPLACE with the 'i' flag for case-insensitive replacement.
REPLACE changes the query result only; the stored value stays as it is until you update it.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Show SKUs with the dashes removed.
Which product codes contain characters other than letters and numbers?