Friday, February 10, 2012

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
Dan
On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.
|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegro ups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>
|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan
|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

No comments:

Post a Comment