How to remove special characters from sql database

Question

How do I remove special characters ALL AT ONCE from a WHOLE DATABASE in sql through phpMyAdmin? I imported a bunch of inventory items into an online invoicing system, and found out that select items with descriptions containing special characters such as (") would cause items not to save correctly.

How would I run a sql command on that table to remove the special characters from all rows in the database at once?

You don't easily. First identify the TEXT-type (char, Varchar, etc) fields that contain the problematic characters, then write an update query that replaces them. You might be better off fixing the input file, I expect. — Erwin Moller, Jul 07 '15 at 13:30
change invoicing system. really. alter the data to comply with a broken system will give you problems in the long run. the right solution is to handle correctly the special chars. — Paolo, Jul 07 '15 at 13:32

score 2 · Accepted Answer · answered Jul 07 '15 at 13:38

2

try this

update mytable
set FieldName = REPLACE(FieldName,'/','')

answered Jul 07 '15 at 13:38

Only because posted first. @n099y Thanks for the heads up caution on mass altering tables, the backup is definitely going to be a given. – Steven Jul 07 '15 at 13:55

score 1 · Answer 2 · edited May 23 '17 at 12:02

1

This looks to be what your looking for.

How can I use mySQL replace() to replace strings in multiple records?

Doing mass updates like this can be dangerous thou.. so I would suggest backing up this table before working on it.

edited May 23 '17 at 12:02

Community

1
1

answered Jul 07 '15 at 13:31

n099y

414
2
16

How to remove special characters from sql database

2 Answers2