Skip to main content

REPLACE

REPLACE(string, search_string, replacement_string) searches for a specific string pattern and replaces it with another string.

Syntax and Parameters​

REPLACE(string, search_string, replacement_string)

  • String - Required. The original string containing the text you want to modify
  • Search String - Required. The pattern you want to find and replace
  • Replacement string - Required. The string that will replace the search string
    • Can be an empty string ('') to remove the search string entirely
    • Case-sensitive by default

Basic Usage​

Using 'Supplier#000366631' as an example:

REPLACE('Supplier#000366631', 'Supplier#', '') -- "000366631"
REPLACE('Supplier#000366631', '#', '-')  -- "Supplier-000366631"
REPLACE('Supplier#000366631', '000', 'XXX')  -- "Supplier#XXX366631"

Common Use Cases​

Removing prefixes:

Clean identifier fields by removing standard prefixes.

-- Basic prefix removal
REPLACE({{SRC}}, 'Supplier#', '') -- 'Supplier#000366631' to '000366631'
REPLACE({{SRC}}, 'CUST_', '') -- 'CUST_12345' to '12345'
REPLACE({{SRC}}, 'ORDER#', '') -- 'ORDER#98765' to '98765'

-- Multiple prefix possibilities
REPLACE(REPLACE({{SRC}}, 'Supplier#', ''), 'SUPP_', '')

Standardizing delimiters:

Phone numbers, dates, and other formatted fields often need delimiter standardization.

-- Phone number formatting
REPLACE({{SRC}}, '.', '-') -- '123.456.7890' to '123-456-7890'
REPLACE({{SRC}}, ' ', '-') -- '123 456 7890' to '123-456-7890'

-- Multiple delimiter cleanup
REPLACE(
    REPLACE(
        REPLACE({{SRC}}, '.', '-'), 
        ' ', '-'
    ), 
    '/', '-'
)   

-- 123.456.7890 to 123-456-7890
-- 123 456 7890 to 123-456-7890
-- 123/456/7890 to 123-456-7890

-- Date formatting
REPLACE({{SRC}}, '/', '-') -- '12/31/2023' to '12-31-2023'

Cleaning data:

Text fields can contain unwanted characters or formatting.

REPLACE(
    REPLACE({{SRC}}, CHAR(13), ' '), 
    CHAR(10), ' '
)
REPLACE({{SRC}}, '  ', ' ')

Using REPLACE in Coalesce​

When using REPLACE in transforms and bulk editing, you can use Helper Tokens:

REPLACE({{SRC}}, 'Supplier#', '')

Expanded examples:

REPLACE("SUPPLIER"."S_NAME", 'Supplier#', '')