MySQL Replace function finds and replaces the occurrences of specified string with the other string.
|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'),
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");|
Posted on: January 20, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles