CODEWITHSUNDEEP
sql-servert-sql
Katana
Katana

Reputation: 421

How to concatenate strings in sql server

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'.

Upvotes: 0

Views: 307

Answers (5)

Mark Sinkinson
Mark Sinkinson

Reputation: 976

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;

Upvotes: 7

Terry Lennox
Terry Lennox

Reputation: 30685

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'

Upvotes: 2

JonWay
JonWay

Reputation: 1735

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;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 3

MJH
MJH

Reputation: 1750

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.

Upvotes: 2

Related Questions