Convert Varchar is inconsistent

Hi All,
I have a source table which contains a customer code in a CHAR(8) column.
The customer codes are only ever 5 or 6 characters long, so in the reporting
tables I am extracting data out to, I have been specifying the customer code
column as a VARCHAR(8). [I am aware that for such a small variance its
debatable whether its worth the overhead of having a VARCHAR column, but jus
t
run with me on this one].
In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
CustomerCode) but some of my tables still contain a padded 8 character
customer code, despite the datatype being VARCHAR(8) and the sp explicitly
stating convert this code to a varchar. I can verify this with the LEN and
DATALENGTH commands. All the tables are in the same database and I havent
been playing with the SET ANSI_PADDING setting either.
I'm thinking that SQL is evaluating whether its worth doing the conversion,
and sometimes it thinks its worthwhile, and other times it doesnt (maybe on
whether it comes accross a 5 or a 6 character customer first?). However, I
cant find anything about this 'machine learning' feature in BOL.
Has anyone come accross this before? and if so, what did you do to force SQL
into storing the data in the column as a VARCHAR?
TIA,
Bill PColumns of type CHAR are padded with space (See ANSI_PADDING in BOL), so whe
n
you convert to varchar you are converting also the spaces and they are not
trimmed in the convertion. You need to rtrim the converted value.
Example:
create table t (
colA char(8) not null
)
insert into t values('a')
insert into t values('bb')
insert into t values('ccc')
insert into t values('dddd')
insert into t values('eeeee')
insert into t values('gggggg')
insert into t values('hhhhhhhh')
select
convert(varchar(8), colA),
datalength(convert(varchar(8), colA)),
rtrim(convert(varchar(8), colA)),
datalength(rtrim(convert(varchar(8), colA)))
from
t
select
convert(varchar(1), space(1)),
datalength(convert(varchar(1), space(1))),
len(convert(varchar(1), space(1)))
drop table t
go
AMB
"Bill P" wrote:

> Hi All,
> I have a source table which contains a customer code in a CHAR(8) column.
> The customer codes are only ever 5 or 6 characters long, so in the reporti
ng
> tables I am extracting data out to, I have been specifying the customer co
de
> column as a VARCHAR(8). [I am aware that for such a small variance its
> debatable whether its worth the overhead of having a VARCHAR column, but j
ust
> run with me on this one].
> In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
> CustomerCode) but some of my tables still contain a padded 8 character
> customer code, despite the datatype being VARCHAR(8) and the sp explicitly
> stating convert this code to a varchar. I can verify this with the LEN an
d
> DATALENGTH commands. All the tables are in the same database and I havent
> been playing with the SET ANSI_PADDING setting either.
> I'm thinking that SQL is evaluating whether its worth doing the conversion
,
> and sometimes it thinks its worthwhile, and other times it doesnt (maybe o
n
> whether it comes accross a 5 or a 6 character customer first?). However,
I
> cant find anything about this 'machine learning' feature in BOL.
> Has anyone come accross this before? and if so, what did you do to force S
QL
> into storing the data in the column as a VARCHAR?
> TIA,
> Bill P|||On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
(snip)
> [I am aware that for such a small variance its
>debatable whether its worth the overhead of having a VARCHAR column, but ju
st
>run with me on this one].
Hi Bill,
It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
characters.
But okay - I'll run with you.

>In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
>CustomerCode) but some of my tables still contain a padded 8 character
>customer code, despite the datatype being VARCHAR(8) and the sp explicitly
>stating convert this code to a varchar.
That's correct. As CHAR(8), the data got padded with spaces. The
conversion to VARCHAR won't remove the trailing spaces.
Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
spaces and really reduce the length.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Alejandro,
Many thanks for that. Thats something that I hadnt considered, but it makes
total sense. I will ensure that I perform RTRIMs in all the SPs to ensure
consistency.
I doesnt answer why some of my tables were RTRIMing themselves and some
werent though? Thats not something I want you to answer as I am more than
happy with the solution, but its still something that I dont at this point
fully understand. If I find out, I will post it to the group.
Thanks once again,
Bill P
"Alejandro Mesa" wrote:
> Columns of type CHAR are padded with space (See ANSI_PADDING in BOL), so w
hen
> you convert to varchar you are converting also the spaces and they are not
> trimmed in the convertion. You need to rtrim the converted value.
> Example:
> create table t (
> colA char(8) not null
> )
> insert into t values('a')
> insert into t values('bb')
> insert into t values('ccc')
> insert into t values('dddd')
> insert into t values('eeeee')
> insert into t values('gggggg')
> insert into t values('hhhhhhhh')
> select
> convert(varchar(8), colA),
> datalength(convert(varchar(8), colA)),
> rtrim(convert(varchar(8), colA)),
> datalength(rtrim(convert(varchar(8), colA)))
> from
> t
> select
> convert(varchar(1), space(1)),
> datalength(convert(varchar(1), space(1))),
> len(convert(varchar(1), space(1)))
> drop table t
> go
>
> AMB
>
> "Bill P" wrote:
>|||Hi Hugo,
If only life were that simple. If I set these up as CHAR(6) or VARCHAR(6)
you can bet your bottom dollar that the some bright spark will create a new
customer with an 8 character code, simply because the ERP system lets them.
But I take your point.
Bill :-)
"Hugo Kornelis" wrote:

> On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
> (snip)
> Hi Bill,
> It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
> bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
> characters.
> But okay - I'll run with you.
>
> That's correct. As CHAR(8), the data got padded with spaces. The
> conversion to VARCHAR won't remove the trailing spaces.
> Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
> spaces and really reduce the length.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 07:07:04 -0800, Bill P wrote:
(snip)
> the ERP system lets them.
Ah, I see how that changes things. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)