Reputation: 296

Update existing column data MySQL

I have a table with speed column in MySQL DB.

speed

100 Mbits
120 Mbits

I am trying to update this column to remove Mbits part from this column.

Can I have an in place query which can do this task?

I tried googling which suggested split_str function which looks complicated for this task.

Any help is much appreciated.

Regards, Madan

Upvotes: 2

Answers (3)

Reputation: 49270

You can use replace to do this.

update mytable set speed = replace(speed, 'Mbits', '')
where lower(speed) like '%mbits%'

Edit: Multiplying by 1000 where speed contains Mbits

update mytable set speed = 1000 * cast(replace(speed, 'Mbits', '') as signed)
where lower(speed) like '%mbits%'

Upvotes: 3

Reputation: 2800

Use this:

Update table set speed=LEFT(speed, INSTR(speed, ' ') - 1)

Result would be from:

100 Mbits
120 Mbits

100
120

Upvotes: 2

Reputation: 667

you can use substring_index. something like:

UPDATE table
SET speed = SUBSTRING_INDEX(speed,'Mbits', 1);
WHERE speed LIKE '%Mbits%'

Upvotes: 2