I can SEE the space in an email address, but SQL SERVER can't?

Hi --

I am trying to clean up some email addresses in sql server. I can see examples of the problematic emails, and they look like this:

abc123 @xyz.com

However, when I try to run a query to find folks like these, say with:

NOT CHARINDEX(' ',LTRIM(RTRIM([Email_Address]))) = 0

or

Email_Address like '% %'

my query returns no results.

Any idea what gives?

Thanks for the quick answers. It was a Non-Breaking Space -- Char(160). The sql ASCII() function came in handy to find it.

From stackoverflow

• Is it definitely a space in there or could it be some other non-printing character?

• Two possibilities occur:

  1. it's not a SPACE character. Check it for TAB or a non-printing ASCII haracter.

  2. there's nothing there at all and your display font is kerned weird.

• It maybe not be the actual space that comes from your space bar. I'd say try to dump the results to a text file, open it with Notepad, copy and paste the "space" from Notepad.

  One of the reasons I hope they don't change Notepad, the lack of support for more advanced characters allows you to do something like this.

  Kezzer : Yup, SQL viewers can render text oddly to ensure fixed-width readability causing characters to look as if they're half-rendered, or with additional space.

• I would dump it to Vim or UltraEdit or [insert text editor of choice] and look at it in hex. Then you can easily tell if this is a space character or something else.