## Syntax ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR Start((START)) --> REGEXP_REPLACE[REGEXP_REPLACE]:::quoted REGEXP_REPLACE --> End((END)) ``` ## Purpose The `REGEXP_REPLACE` SQL function replaces all occurrences matching the regular expression with the replacement value. The replacement value may contain references to matches in the regular expression by using the dollar-sign (') plus the reference number. The regular expression must follow the Microsoft.net regular expression language. See also [[REGEXP_INSTR]] and [[REGEXP_SUBSTR]]. Parameters: - Input (`varchar2`): The text to get the substring from. - Pattern (`varchar2`): Regular expression pattern. - Replacement (optional, `varchar2`): Text to replace with. Defaults to `''`. - Start position (optional, `int32`): The start index from the input. Defaults to `1`. - Appearance (optional, `int32`): Indicating the appearance of the replace operation (use '0' for all appearances). Defaults to `0`. - Match_parameter (optional, `varchar2`): A text literal that lets you change the default matching behaviour of the function. The available options are 'c' for case-sensitive, 'i' for ignore case, 'n' for single-line, 'm' for multi-line and 'x' for ignore pattern white space. Defaults to `''`. Returns: the input with every occurrence of the regular expression pattern replaced with the replacement. ## Examples The following example replaces every digit by `#`: ```sql select regexp_replace('2, 5, and 10 are numbers', '\d', '#') ------------------------ #, #, and ## are numbers ``` The following example extracts the street name from an address using a reference to a match: ```sql select regexp_replace('Zwaanstraat 2 a', '(.*) ([0-9]+)(.*)', '$1') ------------------- Zwaanstraat a ``` The following example replaces the first vowel, ignoring case: ```sql select regexp_replace('INVANTIVE', 'a|e|i|o|u', '*', 1, 1, 'i') ------------------- *NVANTIVE ```