[sql-server] How to concatenate two strings in SQL Server 2005

DECLARE @COMBINED_STRINGS AS VARCHAR(50),
        @STRING1 AS VARCHAR(20),
        @STRING2 AS VARCHAR(20);

SET @STRING1 = 'rupesh''s';
SET @STRING2 = 'malviya';
SET @COMBINED_STRINGS = @STRING1 + @STRING2;

SELECT @COMBINED_STRINGS;

SELECT '2' + '3';

I typed this in a sql file named TEST.sql and I run it. I got the following out put.

+-------------------+
| @COMBINED_STRINGS |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

+-----------+
| '2' + '3' |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

After looking into this issue a bit more I found the best and sure sort way for string concatenation in SQL is by using CONCAT method. So I made the following changes in the same file.

#DECLARE @COMBINED_STRINGS AS VARCHAR(50),
 #       @STRING1 AS VARCHAR(20),
 #       @STRING2 AS VARCHAR(20);

SET @STRING1 = 'rupesh''s';
SET @STRING2 = 'malviya';
#SET @COMBINED_STRINGS = @STRING1 + @STRING2;
SET @COMBINED_STRINGS = (SELECT CONCAT(@STRING1, @STRING2));

SELECT @COMBINED_STRINGS;

#SELECT '2' + '3';
SELECT CONCAT('2','3');

and after executing the file this was the output.

+-------------------+
| @COMBINED_STRINGS |
+-------------------+
| rupesh'smalviya   |
+-------------------+
1 row in set (0.00 sec)

+-----------------+
| CONCAT('2','3') |
+-----------------+
| 23              |
+-----------------+
1 row in set (0.00 sec)

SQL version I am using is: 14.14