How we can REPLACE() values in MySQL8 String Functions?

by · Published · Updated

REPLACE() function change all instances of a fromString to newString within a string. It treats lower and upper case letters differently.  Or it performs a case-sensitive match.

 Note : This function performs a case-sensitive match.

MySQL REPLACE() Functions: Syntax

REPLACE ( string, fromString, newString );
 

MySQL REPLACE() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|left|left|left”]
Name, Required /Optional,Type, Description
string, Required, String , It represents the valid input string.
fromString, Required, String , It represents a valid string to be search in input string.
newString, Required, String , It represents valid string to be replaced with fromString.
[/table]

MySQL REPLACE() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Return, Description
String, It change all instances of a fromString to newString within a string.
NULL, if any argument is NULL.[/table]

MySQL REPLACE() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

REPLACE() Example 1 : Very easy and simple example

We have to REPLACE the string ‘site.’ with ‘for all.’. Below is the example to show it.

mysql> SELECT REPLACE('This is tutorialmines site.','site.','for all.' );
+------------------------------------------------------------+
| REPLACE('This is tutorialmines site.','site.','for all.' ) |
+------------------------------------------------------------+
| This is tutorialmines for all.                             |
+------------------------------------------------------------+
1 row in set (0.00 sec)

REPLACE() Example 2 : Difference between lower and upper case letters

Below is the example where we will find ‘SITE.’, in the ‘This is tutorialmines site.’. Although we have search for the word site, But the case is different. So, nothing will happen in input string.

mysql> SELECT REPLACE('This is tutorialmines site.','SITE.','for all.' );
+------------------------------------------------------------+
| REPLACE('This is tutorialmines site.','SITE.','for all.' ) |
+------------------------------------------------------------+
| This is tutorialmines site.                                |
+------------------------------------------------------------+
1 row in set (0.00 sec)

REPLACE() Example 3 : NULL arguments

If any of the arguments is NULL, it will return NULL. See below example

mysql> SELECT REPLACE(NULL,'SITE.','for all.' ) as First,
    -> REPLACE('Tutorialmines', NULL,'for all.' ) as Second,
    -> REPLACE('Tutorialmines','SITE.', NULL ) as Third;
+-------+--------+-------+
| First | Second | Third |
+-------+--------+-------+
| NULL  | NULL   | NULL  |
+-------+--------+-------+
1 row in set (0.04 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : LOCATE(), HEX(), CONCAT(), CONCAT_WS() , LOWER(), LTRIM(), INSTR(), POSITION().

PHP Related articles : STR_REPLACE(), STR_IREPLACE(), SUBSTR_REPLACE(), STR_REPEAT(), SUBSTR_COUNT(), SUBSTR_COMPARE(), STR_PAD(), PHP STRING FUNCTIONS().

 

You may also like...