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' → '000366631'
REPLACE({{SRC}}, 'CUST_', '') -- 'CUST_12345' → '12345'
REPLACE({{SRC}}, 'ORDER#', '') -- 'ORDER#98765' → '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.

-- Removes line breaks
REPLACE(
    REPLACE({{SRC}}, CHAR(13), ' '), 
    CHAR(10), ' '
)

-- Removes double spaces
REPLACE({{SRC}}, '  ', ' ')

Using REPLACE in Coalesce

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

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

The {{SRC}} token automatically gets replaced with the fully qualified source Node and column name. For example:

• If source column is "SUPPLIER"."S_NAME" it becomes REPLACE("SUPPLIER"."S_NAME", 'Supplier#', '')
• If source column is "CUSTOMER"."C_NAME" it becomes REPLACE("CUSTOMER"."C_NAME", 'Supplier#', '')