Blog

SQL UPDATE REPLACE

In this short article we will see how replace values ​​with SQL. Specifically, and without going into details, I am going to explain how to perform UPDATEs that replace texts in columns.

Following you will find:

  1. Explanation of UPDATE SQL with REPLACE.
  2. Examples of UPDATE REPLACE in SQL.

Content

  • UPDATE Query and REPLACE Function
    • UPDATE REPLACE SQL Example
    • Example 2 UPDATE to replace strings
  • Conclusions from using REPLACE and UPDATE together

UPDATE Query and REPLACE Function

To perform replacements in SQL we will always use the SQL REPLACE function.

This function, which can be used in other queries such as lass SELECT, allows perform substitutions of one searched string for another.

The definition of this query is as follows:

REPLACE( cadena, cadena_busqueda, cadena_sustitucion )

Now that you know the definition of the function, let’s see what an update SQL query would look like together with a REPLACE.

UPDATE REPLACE SQL Example

In the following example you can see a UPDATE query with REPLACE theoretical where all occurrences of the word are replaced hello for the word world.

UPDATE tabla
SET valor = REPLACE(valor, 'hola', 'mundo');

There is not much more secret, however, I would like to add a somewhat more complete example of a query so that you can see that there are no restrictions on the use of, for example, a WHERE.

Example 2 UPDATE to replace strings

In the following example I mix the functionality of the previous query with a condition: that the field worth keep the words inside hello and bye. Also, I modify another column value2 adding 1 to all records.

UPDATE tabla
SET valor = REPLACE(valor, 'hola', 'mundo'), valor2 = valor2 +1
WHERE valor LIKE '%hola%adios%';

Conclusions from using REPLACE and UPDATE together

As you may have seen the use of this binomial of the REPLACE function and the UPDATE query it is really simple and effective.

If these examples have helped you, I would love for you to share your opinion and even contribute your grain of sand so that others can also benefit from your knowledge.

Finally, I would appreciate it if you would share this content so that we can continue to publish useful content for SQL programmers.

Thank you!

Leave a Reply