MySQL Replace

MySQL Replace function finds and replaces the occurrences of specified string with the other string.

MySQL Replace

MySQL Replace

     

MySQL Replace function finds and replaces the occurrences of specified string with the other string.

 

 

 

 

 

Syntax:

REPLACE( String_Text, String_FROM, String_TO);

where String_Text is the original string where replacement is to be performed and returned as a result, String_FROM is the string which is to be replaced and String_To is the string which would be replaced to the occurrences of String_FROM.

For example: In the query given below,  "Roseindia.net" is the original string and all occurrences of "i" string will be replaced by the string "I". The query gives output as RoseIndIa.net.

Query  :   SELECT REPLACE('Roseindia.net','i','I');
Output :   RoseIndIa.net

We can also apply this Replace function on the tables data while inserting or updating table's data. We are explaining you this with one more example. Create a table "mca" in MySQL and fill some data.

Query   CREATE TABLE `mca` (
`id` bigint(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`subject` varchar(255) default NULL,
PRIMARY KEY (`id`)
);

/*Data for the table `mca` */

insert into `mca`(`id`,`name`,`subject`) values (1,'Amit','computer'),(2,'Ramesh','computer'),(3,'Suman','computer'),
(4,'Vineet','Java'),(5,'Sandeep','c++');
 

Output

 

When we execute the following update query on the table "mca". The REPLACE() function will replace every occurrence of "c" in the subject column to "C".

Query  UPDATE `mca` SET `subject` = REPLACE(`subject`,"c","C");
 

Output