MySql Script to search all non ascii and reaplace with space

Question

Can I ask for some sort of script to search for an ascii ranging from DLE way up to US. You can check it at ASCII TABLE.

I have a code which suppposedly search for a non ascii character but I also want to replace it with a space at the same time.

Here's my code

SELECT *
from
`TABLE_NAME`(Sample only)
where COLUMN_NAME(sample only)
like %non-keyboard character(sample only(1st non keyboard character))% or
like %non-keyboard character(sample only(2nd non keyboard character))%.....

The code above is long since I used it from DLE down to US.

Any advice will be greatly appreciated.

See this site http://www.xaprb.com/blog/2006/04/14/bad-character-data-in-mysql/ — Lucas Henrique, May 21 '14 at 00:27

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

You can search range from DLE to US using REGEXP. With REGEXP you can specify non-ascii character like [.DLE.].

SELECT col
FROM tab
WHERE col REGEXP '[[.DLE.]-[.US.]]';

But, Unforfunately there is no way to replace using REGEXP something like REG_STR_REPLACE(). please refere to this

So you should call REPLACE() several times but this is slow. You have three choices.

1.

SELECT REPLACE(REPLACE(REPLACE(col, CHAR(16), ''), CHAR(17), ''), CHAR(18), '')
FROM ...

2. install udf explained in SO

3. do in client side

edited May 23 '17 at 12:06

Community

1
1

answered May 21 '14 at 00:51

Jason Heo

9,956
2
36
64

So Number 1 will actually show if a cell in a column within the table has non keybaord characters? Am I right? – Roi patrick Florentino May 21 '14 at 14:32
And is this correct I used it but never returned an error `UPDATE table SET column = raplce(column, char(16-31), '')` – Roi patrick Florentino May 21 '14 at 14:47

score 0 · Answer 2 · answered May 21 '14 at 00:30

Try this one

update tablename
set columnToCheck = replace(columnToCheck , char(146), '');

or

UPDATE tablename
SET columnToCheck = REPLACE(CONVERT(columnToCheck USING ascii), '?', '')
WHERE ...

Font: http://www.xaprb.com/blog/2006/04/14/bad-character-data-in-mysql/

MySql Script to search all non ascii and reaplace with space

2 Answers2