Hello all,

I just imported an Excel speadsheet into a SQL 2000 database. I'm trying to join that data with some other tables. I noticed that there is a space or what looks like a space after every entry in the column and wherever there is a space between words. I want to join to the data in but I cannot get rid of the space with the rtrim and ltrim functions. And replacing spaces with nothing wouldn't work, one because its not a space and two if it was a space and removed the spaces between words I wouldn't be able to join to the column.

I found a work-around by doing a 'left' on the length - 1 and ended up copying what looked like the space character into a 'replace' function and that worked.

Does anyone know what that character is? In the future I would simply like to replace that character with a space and then 'rtrim/ltrim' the data.

Thank you.

Cheers,
Richard

I think it is not space but tab charecter. you can check for vbTab in replace function.

Om Prakash

Hi Om,

Thank you for your reply.

I tried replace with 'vbTab' and that doesn't remove the space either.

Any other suggestions?

What I was able to do though was to copy the space from the database and replace it with a "real" space from the 'space bar' on the keyboard for entries that have spaces between words. Never have I seen such a problem.

Thanks,
Richard

There are a number of "extended asci" values that can appear as a blank. You need to find out what the value is then use the SQL replace function to get rid of it. Use the ascii() function to find out what it is first though. It would be something along these lines.

select char(255)
select ascii('x')

for the field and row where you get the blank/tab/???
select ascii(right(fieldx,1))

This should give you an asci value, whatever that value is put it in the statement below replacing the 255

REPLACE(fieldx,char(255),'')

This will replace the offending value with nothing, thus removing it.