-1

I uploaded a spreadsheet to my database and some of the emails have spaces after them. I tried using Trim and RTRIM and none of them work. Then I came to think that maybe its some invisible hex code. This is what it looks like when I copy it out

This is what shows in my queue

"john.red@test.com\u00a0\"

this is what it looks like in the database with the space

john.red@test.com

how would I remove this space from all the fields?

Slygoth
  • 333
  • 6
  • 17
  • 2
    Possible duplicate of [How to remove invisible characters in t-sql?](https://stackoverflow.com/questions/1245664/how-to-remove-invisible-characters-in-t-sql).. Also look at [how-do-i-remove-non-breaking-spaces-from-a-column-in-sql-server](https://stackoverflow.com/questions/15214848/how-do-i-remove-non-breaking-spaces-from-a-column-in-sql-server) – Brien Foss Feb 24 '18 at 05:20
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. –  Feb 24 '18 at 08:27

2 Answers2

1

Following code will be helpful to you,

UPDATE Your_Table
SET Your_Column = REPLACE(Your_Column, NCHAR(0x00A0), '')

\u00a0 is the non-breaking space. You can remove it by replacing it with ''.

Abhilash Ravindran C K
  • 1,818
  • 2
  • 13
  • 22
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(0x00A0), '')' at line 2 – Slygoth Feb 27 '18 at 23:36
0

\u00a0 is the NO-BREAK SPACE. It's not a real space and RTRIM will not take it away.

You will have to use REPLACE to remove it:

REPLACE(<column-name>, NCHAR(0x00A0), '')
Ofir Winegarten
  • 9,215
  • 2
  • 21
  • 27