Unable to concatenate Char Datatype and string in SQL

Question

I came across this scenario wherein I tried to concatenate a Char data type variable and a string but the results were pretty surprising.

declare @Var char(10) = 546875.5
set @Var = @Var + '100' 
select @Var

Output: 546875.5

I tried to increase the size of the variable , but still the same result.

declare @Var char(100) = 546875.5
set @Var = @Var + '100' 
select @Var

Output : 546875.5

When I reversed the concatenation, strings did concatenate :

declare @Var char(10) = 546875.5
set @Var = '100' + @Var 
select @Var

Output: 100546875.

Can anyone please help me solve this mystery.

Please note:: I understand that I can convert both before concatenating and that will serve the purpose. But I am not sure why this is not working.

TIA !!

try using Select CONCAT(@Var,'100')

Sabri
– Sabri

2018-04-02 05:12:16 +00:00
Commented Apr 2, 2018 at 5:12 — Sabri
– Sabri, Commented Apr 2, 2018 at 5:12

marc_s · Accepted Answer · 2018-04-02 06:10:22Z

3

@Var is right-padded with spaces because you used char instead of varchar. The spaces take up the entire available length, so there is nowhere for the '100' to be appended.

This can be seen with:

declare @Var char(100) = 546875.5 
set @Var = @Var + '100' 
select replace(@Var, ' ', 'X')

Change @Var to be varchar and you'll be fine. (And you need at least 11 characters).

declare @Var varchar(11) = 546875.5 
set @Var = @Var + '100' 
select @Var

edited Apr 2, 2018 at 6:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 2, 2018 at 5:13

Peter Aylett

7704 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Emdad · Accepted Answer · 2018-04-02 05:37:20Z

2

If you will use char data type, then you need to declare another char type variable and set concat value to that variable.

DECLARE @Var CHAR(10)='546875.5'
DECLARE @FinalVar CHAR(15)
SET @FinalVar=@Var + '100'
SELECT replace(@FinalVar,' ','')

Thanks.

answered Apr 2, 2018 at 5:37

Emdad

8227 silver badges15 bronze badges

Comments

Jayasurya Satheesh · Accepted Answer · 2018-04-02 05:45:46Z

1

All you need is to Trim the leading and trailing spaces before concatenating

declare @Var CHAR(100) = 546875.5
SET @Var = LTRIM(RTRIM(@Var)) + '100' 
SELECT @Var

Result

answered Apr 2, 2018 at 5:45

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Collectives™ on Stack Overflow

Unable to concatenate Char Datatype and string in SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related