SQL Tutorial

SQL REPLACE Function

SQL REPLACE function is used to replace every occurrence of the string which specified as the search_string with replacement_string. The sytax of SQL REPLACE function is the same in almost RDMBS such as Microsoft SQL Server, MySQL and Oracle...Here is the syntax of SQL REPLACE function:

REPLACE (string_expression,
         search_string, 
         replacement_string)

The function is replace all search_string with replacement_string in string_expression. If no match found, the string_expression is not altered. This is example of using SQL REPLACE function:

SELECT REPLACE('replace string','string','function') 
       AS replaced_string

Here is the output:

replaced_string 
----------------
replace function

Another example of using SQL REPLACE function is reformating the output. You can retrieve the data from the database and use apply SQL REPLACE function to the field you want to change without changing actual data. Suppose you have addresses database table which contains colunm street. The data in this table is not standard because there are some records which use abbreviation st. for street.  In this case you can use SQL replace function to reformat it to your own form as follows:

SELECT REPLACE (street,'st.','street') street
FROM addresses
Read On