login

Subscribe to SQL Tutorial website RSS Feed
 » SQL Functions » SQL String Functions » SQL REPLACE Function 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 syntax 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 SQL REPLACE function replaces all search_string with replacement_string in string_expression. If no match found, the string_expression is not altered. This is example of using SQLREPLACE 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 to reformat the output. You can retrieve the data from a database and apply SQL REPLACE function to the field you want to change the format without changing actual value of 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 SQLREPLACE function to reformat it in your own format as follows:

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