How the REPLACE() function works in Mariadb?

Posted on

The REPLACE() function in MariaDB is a string function that allows you to replace all occurrences of a substring within a string with another substring. It is a powerful tool that can be used for a variety of purposes, such as:

  • Correcting spelling mistakes
  • Removing sensitive data
  • Replacing outdated information
  • Converting text to a different format

This article will provide an overview of the REPLACE() function, including its syntax, arguments, and usage. We will also provide several examples to illustrate how the function can be used in different scenarios.

Syntax

The basic syntax of the REPLACE() function is as follows:

REPLACE(str, from_str, to_str)

Where:

  • str is the string that you want to replace the substring in.
  • from_str is the substring that you want to replace.
  • to_str is the substring that you want to replace from_str with.

The REPLACE() function is case-sensitive, so it will only replace occurrences of from_str that match the case of the string you are searching in.

Examples

The following are a few examples of how the REPLACE() function can be used:

Example 1: Correcting a spelling mistake

Suppose you have a table of customer names, and one of the names is spelled incorrectly. You can use the REPLACE() function to correct the spelling mistake as follows:

UPDATE customers
SET name = REPLACE(name, 'John', 'Jon')
WHERE name = 'John';

This will update the name of the customer with the ID of 1 to “Jon”.

Example 2: Removing sensitive data

Suppose you have a table of customer records that contains sensitive data, such as credit card numbers. You can use the REPLACE() function to remove the sensitive data as follows:

UPDATE customers
SET credit_card_number = REPLACE(credit_card_number, 'XXXX-XXXX-XXXX-XXXX', '')
WHERE credit_card_number IS NOT NULL;

This will replace all occurrences of the credit card number with an empty string.

Example 3: Replacing outdated information

Suppose you have a website that contains outdated information about a product. You can use the REPLACE() function to replace the outdated information with the new information as follows:

UPDATE products
SET description = REPLACE(description, 'The old product', 'The new product')
WHERE product_id = 1;

This will replace all occurrences of “The old product” with “The new product” in the description of the product with the ID of 1.

Example 4: Converting text to a different format

Suppose you have a table of text data that you want to convert to uppercase. You can use the REPLACE() function to convert the text to uppercase as follows:

SELECT REPLACE(text, LOWER(text), UPPER(text))
FROM table;

This will return a new table with the text converted to uppercase.

The following are some functions that are related to the REPLACE() function:

  • INSTR(): This function returns the position of the first occurrence of a substring within a string.
  • SUBSTR(): This function returns a substring from a string.
  • LTRIM(): This function removes leading spaces from a string.
  • RTRIM(): This function removes trailing spaces from a string.

Conclusion

The REPLACE() function is a powerful tool that can be used for a variety of purposes. By understanding the syntax and arguments of the function, you can use it to perform a variety of tasks on string data.