I am trying to concatenate strings but it doesn't seem to work. Can somebody tell me what is wrong with the code below?
declare @x varchar = 'a';
SET @x += 's'
SET @x = @x + 'z'
SET @x = concat('x','y')
SELECT @x;
None of the above methods worked. I am getting the output as 'x'.
As per Aaron Bertrand's article here:
https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length
...you should not be declaring VARCHAR without a length.
The problem is, if you don't define a length, SQL Server will assign one for you, and it may not be as wide as you expect. In some scenarios, it will be 1 (yes, that is the number one, not a typo), and in others it will be 30.
The docs are quite clear in this as well:
varchar [ ( n | max ) ]
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
This will work fine:
declare @x varchar(10) = 'a';
SET @x += 's'
SET @x = @x + 'z'
SET @x = concat('x','y')
SELECT @x;
You need a length when you declare varchar(). Otherwise, it is only one character long:
declare @x varchar(255) = 'a';
Once the variable is big enough to store the value, the rest of the code should work.
You need to tell sql the length of your varchar. In most cases sql treat varchar as varchar(1)
declare @x varchar(10) = 'a';
SET @x += 's'
SET @x = @x + 'z'
SET @x = concat('x','y')
SELECT @x;
You need to declare your VARCHAR with a length, try changing it to VARCHAR(20) and see what happens.
Declaring VARCHAR without a length results in Length being 1.
The code below will work, remember you need to declare strings long enough to contain the result.
declare @a varchar(100)
declare @b varchar(100)
set @a = 'This should ';
set @b = 'work';
select @a + @b
If you have shorter strings like this:
declare @a varchar(4)
declare @b varchar(4)
set @a = 'This should ';
set @b = 'work';
select @a + @b
You'll end up with the result:
'Thiswork'
