REPLACE Function - How to find and replace string in value in MySQL

REPLACE Function - How to find and replace string in value in MySQL


REPLACE Function in MySQL can be used to find some string and then replace with new string value you like.

Syntax:


REPLACE(ColumnNameOrValue,old_string,New_string)


Let's create sample table customer and insert some data into it.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Some of the value in firstname has "1" , let's replace that with blank space. Also in phonenumber we have "-" and we would like to replace with ".".

Select firstname,
replace(firstname,'1','') as fname,
phonenumber,replace(phonenumber,'-','.') as pnumber from customer;

How to use Replace function in MySQL - MySQL Developer Tutorial