Categories: MySQL FunctionsPHP

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

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().

 

jyoti rani

Recent Posts

What Is a Progressive Web App? Why Would You Need One?

App usage is growing steadily without showing any signs of slowing down. Hence, it is no surprise that mobile applications…

1 year ago

7 Most Popular Paid Online Advertising Strategy

As the world has grown more digital, businesses have adapted themselves. An effectual adaptation includes online advertising. Offline advertising styles…

1 year ago

The Importance of User-Centered Design in Mobile App Development

Step into a world where apps dance to the user's tune. Picture Instagram, a photo-sharing sensation that swept the globe.…

1 year ago

Healthcare Mobile App Development: A Complete Guide for Founders

COVID-19 has led to a digitalization of lifestyle. As patients are taking their mental and physical health more seriously, healthcare…

1 year ago

Exploring Diverse WordPress Theme Niches: A Comprehensive Guide

Introduction WordPress, an immensely popular content management system (CMS), powers over 40% of the internet. What makes WordPress even more…

1 year ago

8 Awesome Blog Content Ideas for Movers to Skyrocket the SEO

For moving companies trying to capture their market share amidst stiff competition, a tip or two about what they can…

1 year ago