Skip to main content

1E 23.11 (SaaS)





Uses a regular expression to replace a string.


(string)Regular expression in ECMA262 format to be used for matching; enclosing an expression in parentheses will cause the value for that expression to be "captured" and used in the replacement string.

(string) Input string to which the Regular expression will be applied.

(string) Replacement string, using $1, $2, etc. as placeholders for sub-matches. See for example

Return values

(string) The original string with all the matching text replaced with the replacement text.


// This example returns "blue and red"
SELECT REGEXPREPLACE("(.*) and (.*)", "red and blue", "$2 and $1");
// This example takes rows from @iniFileLines, and splits the INI-file style lines 
// (e.g. MyKey=MyValue) into Key and Value columns
// Note the "?" in the regular expression to indicate a non-greedy match
SELECT REGEXPREPLACE("(.*?)=(.*)", Line, "$1") AS Key
,      REGEXPREPLACE("(.*?)=(.*)", Line, "$2") AS Value
FROM   @iniFileLines;
// This example returns "747", i.e. the second (out of three) captures
SELECT REGEXPREPLACE("(.*?)([0-9]+)(.*)", "The 747 is my favourite plane", "$2") AS ModelNumber;