Replace in MySQL

Question

In my database I have a few records like these with 2 columns:

 Id, Name,

11,  Meal 997,
12,  Meal 998,
13,  Meal 999,
14,  Meam 000,
15,  Meam 001,
16,  Meam 002,

But this was an issue in the application with the wrong data due to a logical error. How can we change the records containing Meam to Meal and also continue incrementing?

The above records should be like these:

11,  Meal 997,
12,  Meal 998,
13,  Meal 999,
14,  Meal 1000,
15,  Meal 1001,
16,  Meal 1002,

How can we change the database?

"Meam 001" will be in one columm

What would the query be?

Dynodix · Accepted Answer · 2011-02-23 18:32:08Z

4

update tablename
set name=replace(name,'Meam','Meal 1')
where name like "Meam%"

you can test it with:

select name, replace(name,'Meam','Meal 1')
from tablename

;)

answered Feb 23, 2011 at 18:32

Dynodix

661 bronze badge

Sign up to request clarification or add additional context in comments.

3 Comments

Srinath Over a year ago

seems this was tricky, now it shows as "Meal 1 001", "Meal 1 002". How can we concate as "Meal 1001". thanks for your answer :)

Dynodix Over a year ago

Just change >>replace(name,'Meam','Meal 1')<< with >>replace(name,'Meam ','Meal 1')<< NOTE THE space after Meam !

Srinath Over a year ago

thanks a lot , now i need to focus on Mysql functions seriously :)

user227466 · Accepted Answer · 2011-02-23 17:18:52Z

1

Would not a simple SQL UPDATE query be enough?

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

or have I totally misunderstood the question, apologies if I have...

answered Feb 23, 2011 at 17:18

user227466

Comments

Peter Mortensen · Accepted Answer · 2011-03-09 23:36:06Z

1

update tablename
set name='Meal' , intvalue=concat('1',intvalue)
where name='Meam'

in the table: name is the columname of the column containing Meal or Meam. intvalue is the columname of the column containing the integer number.

I hope it helps.

edited Mar 9, 2011 at 23:36

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 23, 2011 at 17:24

Dynodix

661 bronze badge

1 Comment

Srinath Over a year ago

Hi Dynodix, both will be in one column name ie. column Name contains "Meam 001", "Meam 002". thanks in advance

Collectives™ on Stack Overflow

Replace in MySQL

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related