CODEWITHSUNDEEP
phpmysqlsqlcharacter
maaos
maaos

Reputation: 41

How to delete specific character from a huge records in database MySQL?

I have many records in the database, and I would like to delete a specific character from all records in specific column.

for example I have this table

id cost    distance  
1  $1,519  21.5 miles   
2  $7,987  32.789 miles

I would like to remove all dollar signs and comma in the cost column as well I would like to delete the the word miles from distance column

so I want the final result to be

id cost  distance  
1  1519  21.5  
2  1987  32.789

can you please help how could i do this for a huge number of records?

Upvotes: 2

Views: 1142

Answers (2)

vkamayiannis
vkamayiannis

Reputation: 735

select id,
       replace(substring(price, 2, length(price)), '.', ''),  
       replace(distance, 'miles', '')
from *tablename*

Upvotes: 0

John Woo
John Woo

Reputation: 263803

use REPLACE on this,

UPDATE  tableName
SET cost = REPLACE(REPLACE(cost, '$', ''), ',',''),
    distance = REPLACE(distance, 'miles', '')

Upvotes: 4

Related Questions