| Parameter | Type | Description |
|---|---|---|
input | string | string to match against the regular expression |
regex | string | the regular expression to match |
replacement | string | the string to replace any matched substring |
Returns: string
Example
query:
SELECT REPLACE(sales_agent, "Cress", "Hanson") AS sales_agent,
product,
close_date,
close_value
FROM sales_pipeline
WHERE deal_stage = "Won"
AND product LIKE "gtx%"results:
| sales_agent | product | close_date | close_value |
|---|---|---|---|
| Moses Frase | GTX Plus Basic | 2017-03-01 | 1,054 |
| Darcel Schlecht | GTXPro | 2017-03-11 | 4,514 |
| Cassey Hanson | GTXPro | 2017-03-02 | 4,711 |
| Corliss Cosme | GTXPro | 2017-06-28 | 5,133 |
| Daniell Hammack | GTX Plus Pro | 2017-05-15 | 4,848 |
| Daniell Hammack | GTXPro | 2017-03-03 | 4,475 |
| Darcel Schlecht | GTXPro | 2017-03-10 | 3,763 |
| Darcel Schlecht | GTXPro | 2017-03-05 | 5,006 |
| Gladys Colclough | GTXPro | 2017-06-15 | 4,498 |
| Gladys Colclough | GTXPro | 2017-03-24 | 4,375 |
The second argument in the REPLACE function is written in the standard Java regular expression format and is case sensitive. In a standard Java regular expression the . stands as a wildcard for any one character, and the * means to repeat whatever came before it any number of times. In a .* regular expression, the Java single wildcard character is repeated, effectively making the .* regular expression operate the same way as the * wildcard does elsewhere in SQL.