CODEWITHSUNDEEP
mysqlmariadbtrim
Sougata Bose
Sougata Bose

Reputation: 31739

Mysql TRIM() not working

I am not sure why but TRIM() or RTRIM() not working for me.

SELECT TRIM('YV4.3x2.8SA-2 ');

is returning 'YV4.3x2.8SA-2 '.

I can not remove all the spaces as the string may contain space in between.

Upvotes: 1

Views: 2346

Answers (2)

lc.
lc.

Reputation: 116448

So, that last character is 0xC2A0, not 0x20 (space). That is UTF-8 encoding for Unicode code point U+00A0, also known as a non-breaking space.

To remove these using TRIM(), pass convert(0xC2A0 using utf8mb4) for remstr:

select TRIM(convert(0xC2A0 using utf8mb4) FROM 'YV4.3x2.8SA-2 ');

(SQL Fiddle demo)

If you potentially have a mix of these characters and spaces, the easiest method to remove both would be to first replace the NBSP's with regular spaces, then pass the result through TRIM():

select TRIM(REPLACE('YV4.3x2.8SA-2 ', convert(0xC2A0 using utf8mb4), ' '));

(SQL Fiddle demo, with a mix of spaces thrown in)

Upvotes: 7

J.Dev
J.Dev

Reputation: 39

Try this SELECT TRIM(BOTH ' ' FROM 'YV4.3x2.8SA-2 ');

Upvotes: 0

Related Questions