This function replaces occurrence(s) of the matching pattern in the source_string with a specified replace_string, allowing complex search-and-replace operations. The traditional REPLACE SQL function substitutes one string with another. Assume your data has extraneous spaces in the text and you would like to replace them with a single space. With the REPLACE function, you would need to list exactly how many spaces you want to replace. However, the number of extra spaces may not be the same everywhere in the text.
This function returns the a substring matching the regular expression.
This example has three spaces between Joe and Smith. The REPLACE function's parameter specifies that two spaces should be replaced with one space. In this case, the result leaves an extra space where there were three spaces in the original string between Joe and Smith.
SELECT REPLACE('Joe Smith', ' ', ' ') AS REPLACE_NORMAL, REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS REGEXP_REPLACE; REPLACE_NORMAL REPLACE_REGEXP VARCHAR VARCHAR ______________________________________________ Joe Smith Joe Smith