Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though, SQL Server is now in moving very fast and have proved many times a good enterprise solution, it does not have some basic functions. I naturally asked him for example and he suggested CONCAT() which exists in MySQL and Oracle.

The answer is very simple – the equalent function in SQL Server to CONCAT() is ‘+’ (plus operator without quotes).

Method 1: Concatenating two strings

SELECT 'FirstName' + ' ' + 'LastName' AS FullName


Method 2: Concatenating two Numbers

SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10))

Method 3: Concatenating values from table columns

SELECT FirstName + ' ' + LastName
FROM AdventureWorks.Person.Contact

Well, this may look very simple but sometime it is very difficult to find the information for simple things only.

Do you have any such example which you would like to share with community?

Reference: Pinal Dave (http://blog.sqlauthority.com)