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

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.