Concatenating String in Transact SQL
Concatenating String in Transact SQL
By : Kasim Wirama, MCDBA, MCITP, MVP SQL Server
I would like to discuss about concatenating string in Transact SQL (T-SQL). T-SQL is scripting language in SQL world and has powerful performance when it is used in set-based oriented way. One common requirement is to display result of some values coming from a column and they are separated by certain character, such as comma. For example : I would like to display customer list served by each employees in orders table database Northwind. Let’s take a look how to achieve this in SQL Server 2000 and then compare with SQL Server 2005-later.
In SQL Server 2000 and previous version
You can output customer list for a given employee by the following T-SQL
DECLARE @customer varchar(8000);
SELECT @customer = COALESCE(@customer + ‘, ‘, ‘’) + CAST(CustomerID AS VARCHAR(5))
FROM dbo.Orders
WHERE EmployeeID = @EmployeeID
RETURN @customer;
You can encapsulate the script above into function here :
CREATE FUNCTION dbo.GetCustomerList
(@EmployeeID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @customer varchar(8000);
SELECT @customer = COALESCE(@customer + ‘, ‘, ‘’) + CAST(CustomerID AS VARCHAR(5))
FROM dbo.Orders
WHERE EmployeeID = @EmployeeID
RETURN @customer;
END;
After creating the function, you can called the function for getting customerlist for each employee.
SELECT EmployeeID, dbo.GetCustomerList(EmployeeID)
FROM dbo.Employees;
In SQL Server 2005 and later version
You can use combination of STUFF and FOR XML PATH combination as shown here :
DECLARE @customerList varchar(8000);
SET @ customerList = STUFF((select + ', ' + customerID FROM dbo.Orders WHERE employeeID = @employeeID FOR XML PATH ('') ),1,2,'');
RETURN @customerList ;
With similar way, encapsulate it into function and it is ready to be call by other query.
Which one is recommended way?
Both returns same result but concatenation in SQL Server 2000 harness undocumented recursive call. It is side effect from query engine and it is not an intended capability from the engine itself. So it is not guaranteed it will be continually supported in future version of SQL Server. If you use SQL Server 2005 or later, it is better you change such a recursive call into more elegant solution using proprietary STUFF and FOR XML PATH though it is not in ANSI standard.