Replace Function in MariaDB [9 Examples]

In this MariaDB tutorial, we will learn about the ‘MariaDB replace function‘ and cover the following topics.

• MariaDB replace
• MariaDB replace into
• MariaDB replace regex
• MariaDB replace update table
• MariaDB replace text
• MariaDB replace null with 0
• MariaDB replace value
• MariaDB replace character in string
• MariaDB replace special characters

MariaDB Replace Function

The REPLACE function is used to replace all occurrences of the string with some string (new string). The REPLACE function performs case-sensitive which means it is sensitive to the capitalization of letters.

For example, ‘United States‘ and ‘united States‘ are different words because the ‘U’ is in uppercase in the first word and lower in the second word.

The REPLACE function syntax.

REPLACE(source_str,from_str,to_str);

Where source_str is the source string, from_str is a substring that the REPLACE function finds in the source_str and to_str is a replacement string where all the occurrences of from_str substring which is found in the source_str are replaced by to_str.

Let’s check the with example.

SELECT REPLACE('united States','u','U');

Here in the above code, we are replacing the lowercase letter ‘u’ in the word ‘united States’ with the uppercase letter ‘U’ using the REPLACE function.

From the output, we can see that the REPLACE function has replaced the lowercase letter ‘u’ with the uppercase ‘U’.

Also check: MariaDB import CSV

MariaDB replace into

In MariaDB, The REPLACE works the same as the INSERT statement except that if there is the old row in the table has the exact value as a new row for a PRIMARY KEY or a UNIQUE index, the new row is inserted after deleting the old row.

The syntax is given below.

REPLACE
    [INTO] tbl_name
    SELECT ...
[RETURNING select_expr 
      [, select_expr ...]]

Create the new table as rt that will contain the name of animals and insert the data also as given below.

CREATE TABLE rt(id INT,animal_name VARCHAR(100));

INSERT INTO rt(id,animal_name)VALUES(1,'Lion'),(2,'Leopard'),(3,'dog');

After creating the above code, let’s insert the new value using the REPLACE INTO statement.

REPLACE INTO rt VALUES(4,'Tiger');

Now view the table rt.

SELECT * FROM rt;

From the above output, we can see that we have inserted the new value as Tiger in the rt table using the REPLACE INTO statement.

Read: How to Grant User Access to a MariaDB Database

MariaDB replace regex

The REGEXP_REPLACE function is used to replace the string using the regular expression pattern matching. It replaces all the occurrences of the substring in the string with a new string using regular expression.

The syntax of the REGEXP_REPLACE is given below.

REGEXP_REPLACE(source_str, pattern, replace)

where,

• source_str: It is the source string.
• pattern: It is the pattern that matches with string or substring that is found in the source_str.
• replace: It is the replacement string that replaces the string with a new substring or string.

Let’s understand with an example.

SELECT REGEXP_REPLACE('hello456','[0-9]','');

The above code will remove the digit from the source_str ‘hello456’, and pattern ‘[0-9]’ represents all the digits from 0 to 9.

Read: How to Create Database in MariaDB

MariaDB replace update

The REPLACE FUNCTION can be used with an UPDATE statement to update the value of the columns in MariaDB.

Let’s see with the example.

UPDATE rt t
     SET t.animal_name = REPLACE (t.animal_name, 'Tiger', 'Elephant');

Here in the above code, we are updating the column animal_name with replace function using the UPDATE statement. where t is the alias of table rt that we have created above sub-section, where the SET command is used to set the column values.

After the equal sign or assignment operator, the REPLACE FUNCTION is used to replace the column t.animal_name values and this contains t.animal_name as source_str, ‘Tiger’ as from_str, and ‘Elephant’ as to_str.

Read: How to create a user in MariaDB

MariaDB replace text

The REPLACE FUNCTION can replace the string in the long text with another string, but it will not replace all the text until specified in the pattern.

Now run the below query to replace the string in the text.

SELECT REPLACE('Dog is running with another dog on the street','dog','Dog');

In the above code, REPLACE function contains source_str as ‘Dog is running with another dog on the street’, from_str as ‘dog’, and to_str as ‘Dog’. Here the word dog which started with lowercase ‘d’ is replaced by the uppercase word ‘Dog’.

Read: MariaDB Vs SQLite

MariaDB replace null with 0

In MariaDB, the NULL value can be replaced by 0 using the UPDATE statement, the null value denotes to lack of value or does not point to any object.

Here we can not replace the null value directly with zero using the REPLACE function, because there is no pattern exist that can be used with REPLACE function to detect the null value.

Create a new table as null_demo, insert the records and view the table using the below query.

CREATE TABLE null_demo(id INT,value_1 INT, value_2 INT);

INSERT INTO null_demo(id,value_1)VALUES(1,34),(2,99),(3,67);

SELECT * FROM null_demo;

In the above output, there is column value_2 that contains the null value, so we will replace this null value with 0.

UPDATE null_demo
  SET value_2 = 0 WHERE value_2 IS NULL;
  
  
SELECT * FROM null_demo;

Read: MariaDB Timestamp

MariaDB replace value

The MariaDB REPLACE FUNCTION can also replace the values, here we will consider the value as an integer.

Let’s check with an example.

SELECT REPLACE('932838242','2','6');

Here in the above code, we have supplied the value 932838242 as source_str, 2 as from_str, and 6 as to_str to REPLACE function that replaces the value 2 with 6.

MariaDB replace character in string

In MariaDB, The REPLACE FUNCTION can also replace the characters in the string, the string is a sequence of characters.

Let’s replace the character from the string.

SELECT REPLACE('United States','s','');

The above code will replace the lowercase ‘s’ in the string ‘United States’ with nothing, where ‘Unites States’ is a source_str, ‘s’ is from_str, and double single quote (”) is to_str.

As we can see in the above result, character ‘s’ is replaced with nothing or a double single quote that represents nothing in the string United States.

Read: MariaDB ERROR 1064

MariaDB replace special characters

The REPLACE FUNCTION can also replace the special characters like @, #, $, %, ^, etc.

Let’s understand with an example.

SELECT REPLACE('@hell','@',' ');

In the above code, we are replacing the special character @ with space using the replace function. where @hell is source_str, ‘@’ is from_str, and ‘ ‘ is a space as to_str.

You may also like to read the following MariaDB articles:

• How to Create Function in MariaDB
• How to Create Trigger in MariaDB
• MariaDB Vs SQL Server
• How to Drop Column from MariaDB Table
• How to Add Column in MariaDB
• MariaDB LIMIT

Here we learned, how to use replace function in MariaDB and also covered the following topics.

• MariaDB replace
• MariaDB replace into
• MariaDB replace regex
• MariaDB replace update table
• MariaDB replace text
• MariaDB replace null with 0
• MariaDB replace value
• MariaDB replace character in string
• MariaDB replace special characters