Little quirk dealing with For Xml

I've been using For Xml in T-SQL for a long time to concatenate things without really investigating the ins and outs of it.

Create  Table #Orders (
        OrderNumber Int Primary Key
)

Insert  Into #Orders
Values  ('123'), ('124'), ('125')

Select  Replace(OrderNumbers, ' ', 'x')
From    (
        Select  (
                Select  Cast(OrderNumber As Nvarchar(Max)) + ',' As [data()]
                From    #Orders
                For Xml Path ('')
                ) As OrderNumbers
         ) b

Drop Table #Orders

Returns:

123,x124,x125,x

What I didn't notice before is that you can use a single comma as a delimiter, you don't need to add a space yourself because the For Xml will add a space afterwards anyway.

And the end of the string always ends in a comma and a space so what happens when you want to strip it off? You use something like this:

Select  Left(OrderNumbers, Len(OrderNumbers) - 1) As OrderNumbers

You may be asking why we are removing 1 character instead of two (the comma and the space). It's because in Len returns the length of a string minus trailing spaces.

PS: A cute way to get the trailing spaces included in Len is to Replace(' ', '*') when passing it in.

Share on: