CODEWITHSUNDEEP
mysqlsql-update
Tales
Tales

Reputation: 1923

Update cell content if a string is present on the cell MySQL

I have a table with a lot of rows (around 1,500). In the column city_name some cells have the city name and end with (CD). I want those cells with the (CD) to be updated without the (CD). This is a preview of my table:

+---------+-----------------------+---------+
| city_id | city_name             | id_dept |
+---------+-----------------------+---------+
|       1 | El Encanto (CD)       |       1 |
|       2 | La Chorrera (CD)      |       1 |
|       3 | La Pedrera (CD)       |       1 |
|       4 | La Victoria (CD)      |       1 |
|       5 | Leticia               |       1 |
|       6 | Miriti Parana (CD)    |       1 |
|       7 | Puerto Alegria (CD)   |       1 |
|       8 | Puerto Arica (CD)     |       1 |
|       9 | Puerto Nariño         |       1 |
|      10 | Puerto Santander (CD) |       1 |
+---------+-----------------------+---------+
10 rows in set (0.00 sec)

And this is what i want to have:

+---------+-----------------------+---------+
| city_id | city_name             | id_dept |
+---------+-----------------------+---------+
|       1 | El Encanto            |       1 |
|       2 | La Chorrera           |       1 |
|       3 | La Pedrera            |       1 |
|       4 | La Victoria           |       1 |
|       5 | Leticia               |       1 |
|       6 | Miriti Parana         |       1 |
|       7 | Puerto Alegria        |       1 |
|       8 | Puerto Arica          |       1 |
|       9 | Puerto Nariño         |       1 |
|      10 | Puerto Santander      |       1 |
+---------+-----------------------+---------+
10 rows in set (0.00 sec)

Well I understand I can select all the rows with (CD) using:

SELECT * FROM city WHERE city_name LIKE ( "%(CD)%" )

But how do I remove the (CD):

UPDATE city SET city_name = "" WHERE city_name LIKE( "%(CD)%" )

Upvotes: 0

Views: 58

Answers (2)

lc.
lc.

Reputation: 116528

If it always ends in " (CD)" and you want to chop those off the end, you can do this:

UPDATE city
SET city_name = LEFT(city_name, CHAR_LENGTH(city_name) - 5)
WHERE city_name LIKE '% (CD)';

Upvotes: 1

Momo
Momo

Reputation: 2491

You can use the REPLACE() function

UPDATE city SET city_name = REPLACE(city_name, '(CD)', '') 
WHERE city_name LIKE( "%(CD)%" )

Upvotes: 1

Related Questions