problems with spaces

Question

problems with spaces

mark lake

Say Hey Kid

Points: 702
More actions
July 11, 2005 at 5:14 am

#186581

I have a table in one of my databases that have lots of spaces at the end of fields so instead of a surname field containing:

Smith
it contains
Smith and ends about there.

I've tried various updates including rtrim, and using a left(surname,len(surname)) . However is always ends up with spaces at the end of the field.

Could anyone offer me any assistance on how to solve this issue? Thanks very mich

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Can you post the field definition? That may go a long way of explaining the problem. If it is CHAR then you will ALWAYS have spaces if VARCHAR then you WONT

Good Hunting!

AJ Ahrens

webmaster@kritter.net

mark lake Say Hey Kid Points: 702 More actions · Answer 2

Thanks for making me look!

I was pretty certain I'd set the fields up as varchars but sure enought they are all chars.

Thanks.

David Burrows SSC Guru Points: 65143 More actions · Answer 3

....if VARCHAR then you WONT

Not entirely true.

varchar columns may or may not have trailing spaces, they are ignored for comparison and LEN but they are still there, e.g

DECLARE @value varchar(20)

SET @value = 'abc '

SELECT '"' + @value + '"'

SELECT LEN(@value)

SELECT DATALENGTH(@value)

IF @value = 'abc' PRINT 'yes'

will result in

"abc "

3

7

yes

but you can remove them with RTRIM and REPLACE however

Far away is close at hand in the images of elsewhere.
Anon.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

David,

You are absolutely correct. IF you UPDATE/INSERT spaces into a VARCHAR field THEN they will be there. I was thinking more in terms of UPDATE/INSERT "Smith" which would not have trailing spaces.

Thanks for setting me straight

Good Hunting!

AJ Ahrens

webmaster@kritter.net

David Burrows SSC Guru Points: 65143 More actions · Answer 5

Thanks AJ

Just returning the favour, you have set me straight on many an occasion

Far away is close at hand in the images of elsewhere.
Anon.

steve block SSCertifiable Points: 7698 More actions · Answer 6

Another point, if you change a field from char to varchar (enterprise manager) the spaces will remain from the old definition until they are removed.

Steve

sara karasik SSCommitted Points: 1573 More actions · Answer 7

if you still have trouble with the spaces, they might be tab characters, not spaces! check for char(9)!