CHAR column problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CHAR column problem

Author

Topic

Rootman
Starting Member

15 Posts

Posted - 2006-07-11 : 10:26:03

I have a column in my db which is char(40).
There are entries in this with the same text, but some come up with a length of 24 and some 25. This sugggests to me that there is a blank trailing space. Am I right?

However, if I update it to iteslf using a Rtrim, nothing changes.

Is is something to do with ANSI padding?

How can I get these entries to be identical, please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-11 : 10:29:11

char column keeps trailing spaces. You need to use varchar datatype if the data varies in length

Madhivanan

Failing to plan is Planning to fail

Rootman
Starting Member

15 Posts

Posted - 2006-07-11 : 10:40:21

So why do some entries show a lenght of 24, and others 25 for identical text?

~Sorry if I am being obtuse.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-11 : 10:45:04

quote:
Originally posted by Rootman

So why do some entries show a lenght of 24, and others 25 for identical text?

~Sorry if I am being obtuse.

Rootman - What has led you to be believe the text is identical?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Rootman
Starting Member

15 Posts

Posted - 2006-07-11 : 10:48:07

This is what is returned when I select the length and the value of the column is question. When I say identical, I mean that it looks identical. What I want to do is make it truly identical!

24 NOM. BALANCE ARRANGEMENT
25 NOM. BALANCE ARRANGEMENT

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-11 : 10:51:52

It could be something like this...

--data
declare @t table (c1 char(40))
insert @t
          select 'NOM. BALANCE ARRANGEMENT'
union all select 'NOM. BALANCE ARRANGEMENT' + char(9)

--calculation
select len(c1) as length, * from @t

/*results
length      c1                                       
----------- ---------------------------------------- 
24          NOM. BALANCE ARRANGEMENT                
25          NOM. BALANCE ARRANGEMENT	               
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Rootman
Starting Member

15 Posts

Posted - 2006-07-11 : 11:12:57

OK, thanks.

So if I do an udate t1 set c1 = left(c1,24)
then the addidional char(9) should be eliminated.

Thanks you again

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-11 : 11:18:02

quote:
Originally posted by Rootman

OK, thanks.

So if I do an udate t1 set c1 = left(c1,24)
then the addidional char(9) should be eliminated.

Thanks you again

Well, it might not be a char(9) character, but if it was, that should work for this particular case, yes. Alternatively, you can use replace.

You can use the ASCII function to figure out what the character is. e.g.

--data
declare @t table (c1 char(40))
insert @t
          select 'NOM. BALANCE ARRANGEMENT'
union all select 'NOM. BALANCE ARRANGEMENT' + char(9)

--calculation
select len(c1) as length, ascii(right(rtrim(c1), 1)) as LastCharacter, * from @t

/*results (1)
length      LastCharacter c1                                       
----------- ------------- ---------------------------------------- 
24          84            NOM. BALANCE ARRANGEMENT                
25          9             NOM. BALANCE ARRANGEMENT
*/

update @t set c1 = replace(c1, char(9), '')
select len(c1) as length, * from @t

/*results (2)
length      c1                                       
----------- ---------------------------------------- 
24          NOM. BALANCE ARRANGEMENT                
24          NOM. BALANCE ARRANGEMENT                
*/