CODEWITHSUNDEEP
phpmysqlreplace
John M
John M

Reputation: 14668

UPDATE MySQL table that contains comma (,) in the field

Imported into a MySQL table is a report that contains a user generated field value.

Possible field values include:

All the example values represent the same thing but are typed in differently (I don't control the input system).

I would like to have all the values use this format:

I was trying to run this PHP query against the database to fix the comma values:

$sql = "UPDATE tableA
SET unit_number = replace(unit_number,'FRGN,','FRGN_')
WHERE unit_number like 'FRGN,%'";

But this doesn't seem to be working.

Do I need to escape the comma in the query in order for it to work?

Upvotes: 1

Views: 1158

Answers (1)

Scoutman
Scoutman

Reputation: 1630

Try this:

$sql = "UPDATE tableA
SET unit_number = concat('FRGN_', replace(replace(replace(unit_number,'FRGN,','') ,'FRGN_',''), 'FRGN', ''))";

or

$sql = "UPDATE tableA
SET unit_number = concat('FRGN_', replace(replace(text,'FRGN,','') ,'FRGN',''))
WHERE unit_number NOT LIKE 'FRGN\_%'";

Upvotes: 1

Related Questions