replace sql code example

Example 1: sql query with replace function

##sql query with replace function
#syntax
UPDATE tableName
SET  column_name = REPLACE(column_name, 'fromStringValue', 'toStringValue');

#Example 
Update  tbl_employee
Set designation = REPLACE(designation, 'SEO', 'Developer');

Example 2: mysql replace

REPLACE(str, find_string, replace_with)

Example 3: sql replace character in string in all records

UPDATE employees 
SET 
    phone_number = REPLACE(phone_number, '.', '-');

Example 4: replace string value in sql

UPDATE tableName  SET  fieldName = REPLACE(fieldName, 'fromStringValue', 'toStringValue');

Example 5: replace \n sql

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

Example 6: MySQL REPLACE

The MySQL REPLACE statement is an extension to the SQL Standard. The MySQL REPLACE statement works as follows:

Step 1. Insert a new row into the table, if a duplicate key error occurs.

Step 2. If the insertion fails due to a duplicate-key error occurs:

Delete the conflicting row that causes the duplicate key error from the table.
Insert the new row into the table again.
To determine whether the new row that already exists in the table, MySQL uses PRIMARY KEY or UNIQUE KEY index. If the table does not have one of these indexes, the REPLACE works like an  INSERT statement.

To use the REPLACE statement, you need to have at least both INSERT and DELETE privileges for the table.

Notice that MySQL has the REPLACE string function which is not the REPLACE statement covered in this tutorial.

The following illustrates the syntax of the REPLACE statement:

REPLACE [INTO] table_name(column_list)
VALUES(value_list);