REPLACE

A string function used in replace operations to modify strings via pattern matching. For more information on the Java format for regular expressions see: https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html.

Parameter Type
arg0
string
arg1
string
arg2
string
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.