Transact-SQL string self-concatenate [closed]

Question

I am new in SQL and want to create some UDF function for MS SQL Server (MS SQL Server 2012), but the very simple expression is not working. I simply want to concatenate 2 strings and save the result to the 1st one:

DECLARE @ret   CHAR (20)
SET @ret = '4'
SET @ret = @ret + '55'

After executing, @ret remains at 4! Why? If I introduce another variable for '4', it works. How can I overcome the problem?

Answer 1

That's because you use the char data type.

When you store the value '4' in a char(20), it becomes '4___________________' (20 characters long, the _ represents spaces here).

When you concatenate '4___________________' and '55' you get '4___________________55' (22 characters long).

When you store that back in the char(20) variable, it will be truncated to 20 characters, and you get '4___________________'.

Answer 2

When you have a CHAR(20) the first SET will actually store '4' with 19 blank characters at the end. This is because the CHAR datatype is fixed width, and will pad your data with blank characters until it gets to the size, 20 here.

When you concatenate the '55' onto the end, it will be the 21st and 22nd characters in the string, and fall off when you try to store it inside @ret, which can only hold the first 20 characters.

Using an VARCHAR will solve your problem, allowing @ret to be the exact size you require.

DECLARE @ret VARCHAR(20);
SET @ret = '4';
SET @ret = @ret + '55';