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 |
|
