Replace Function in MySQL: How to Find and Replace Strings
Hey there! 👋 Today, we're diving into the nifty world of the REPLACE function in MySQL. This little gem allows us to find specific strings and swap them out with new ones of our choosing. Neat, right?
Alright, let's break down how to use it:
REPLACE(ColumnNameOrValue, old_string, New_string)
Pretty straightforward, isn't it? Now, let's take a hands-on approach and use an example. Imagine we have a table called "customer" and we want to play around with the data.
First, we'll create the "customer" table:
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);
Next, we'll insert some sample data into it:
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');
Now, let's get our hands dirty with the REPLACE function. In the "firstname" column, some values have a pesky "1" that we want to get rid of. Additionally, in the "phonenumber" column, those pesky dashes "-" are bugging us, so we'll replace them with dots ".". Here's how:
SELECTfirstname,REPLACE(firstname, '1', '') AS fname,phonenumber,REPLACE(phonenumber, '-', '.') AS pnumberFROM customer;
And voilà! You've just harnessed the power of the REPLACE function in MySQL to spruce up your data. If you're interested in learning more about how to use the Replace function in MySQL, check out our MySQL Developer Tutorial for an in-depth guide. Happy coding! 🚀