Mysql TRIM/REPLACE not working for my Query

Question

A column has some string value with lot of spaces and tabs. i am unable to trim these spaces and white spaces. e.g select trim('column_name') from table_name;

I am not sure how much space is in the string . it may 2 for one string and 12 for other. so replace() is also not working.

Please check http://stackoverflow.com/questions/6940646/mysql-how-to-remove-double-or-more-spaces-from-a-string — hasnat, Jun 16 '16 at 13:29
Do you have a known mix of space characters and tab characters `\t`? — Michael Berkowski, Jun 16 '16 at 13:30

score 1 · Answer 1 · answered Jun 16 '16 at 14:42

1

I got the answer by using the following:

trim(replace(convert(column_name USING ascii),'?',''))

answered Jun 16 '16 at 14:42

Param

55
1
6

I am not sure I follow you statement. But it did lead me to an answer for me. I had an ascii char 0xC2A0 non-breaking space. Finally looking up trimming ascii chars I found I could use this stmt update table set code = trim(char(0xC2A0) from code) where id = 13087; ref: https://michaelborn.me/entry/trimming-tab-characters-in-mysql – gstlouis Jul 29 '20 at 20:03

score 0 · Answer 2 · answered Jun 16 '16 at 14:34

0

MySQL TRIM() does not affect tabs unless they are specified. If you have multiple mixed tabs and spaces, you might have to replace the tabs with spaces and then trim the results.

SELECT TRIM(REPLACE(column_name, CHAR(9), '    ')) FROM table_name;

answered Jun 16 '16 at 14:34

Alien Technology

1,760
1
20
30

Mysql TRIM/REPLACE not working for my Query

2 Answers2