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
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.