Skip to main content

TSQL - Removing a trailing comma from a list of concatenated values

On a semi-regular basis I'd need to squish a list of comma-delimited values all concatenated together into one field, so I'd use this old snippet:

SELECT LEFT(ConcatValues, LEN(ConcatValues)-1) AS 'TheValues'
FROM SomeTable t
      CROSS APPLY ( SELECT    t2.SomeField + ', ' AS [text()]
                    FROM      SomeTable t2
                    WHERE     t.SomeField = t2.SomeField

                    FOR XML PATH('') ) C (ConcatValues)

Problem is when the concatenated field has length zero, LEFT gets a -1 length and returns an error:

Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

So today I had a look around and found a better way from this StackOverflow thread. Just replace the LEFT function with this:

STUFF(ConcatValues, LEN(ConcatValues), 1, '')

It gives the exact same results except that the STUFF function is way more tolerant of being given inappropriate length parameters and just returns NULL (which is exactly what I want) instead of throwing errors.  From the MSDN documentation:

"If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string"


  1. Within your existing SELECT:

    STUFF((SELECT ', ' + table.withmultiplevalues AS [text()]
    FROM dbo.locationoflist table
    WHERE table.fromouterselect = table.relatingvalue
    FOR XML PATH('')
    ), 1, 1, '' )
    AS [name of conclist]

    1. Huh, that's pretty cool! This would definitely work better inline in a SELECT since rather than having the "conclist" referring to the CROSS APPLY which is possibly several scrolls down, you could put the whole lot in one place... Much easier to read!


Post a Comment

Popular posts from this blog

Using Log4Net to use both event log and a rolling log file

Here's the config section, note that the applicationNameproperty in the EventLogAppender needs to be the same as the event source in the windows event log that you want to log to.  If the event source doesn't exist, that appender won't work.  In this particular project I create that during install using WiX (which is covered in another post)

    <appendername="RollingLogFileAppender"type="log4net.Appender.RollingFileAppender">      <filevalue="log.txt" />      <datePatternvalue="dd-MM-yyyy" />      <appendToFilevalue="true" />      <locationinfovalue="false" />      <rollingStylevalue="Size" />      <maximumFileSizevalue="1MB" />      <maxSizeRollBackupsvalue="10" />      <staticLogFileNamevalue="true" />      <layouttype="log4net.Layout.PatternLayout">        <conv…

Using WiX to create an event source during install of a .NET framework project

Edit: so I guess I wasn't the only one confused with this stuff, as it's been my most popular post by far!  If I've helped you out or saved you some time, please let me know in the comments :)

In order for this to work, you have to add references to WixUtilExtension and WixNetFxExtension to your WiX project.  Once that's done, add this inside a <Component> element:

<Util:EventSourcexmlns:Util=""Name="EVENTSOURCEGOESHERE"Log="Application"EventMessageFile="[NETFRAMEWORK40FULLINSTALLROOTDIR]EventLogMessages.dll" />
Obviously replace EVENTSOURCEGOESHERE with your event source name.  NETFRAMEWORK40FULLINSTALLROOTDIR is a property set by the WixNetFxExtension which stores the path to the .NET framework v4 directory, but you can replace this with the corresponding property for the directory containing the relevant EventLogMessages.dll file.  So if you're using the .NET framewo…

Umbraco Deployment Checklist

This is primarily aimed at deploying from Visual Studio to an Azure Web App + Azure SQL database, feel free to skip bits that aren't relevant if you're doing other things.

Prerequisites for developing locally:
Visual Studio (obviously)
Ensure IIS URL Rewrite module is installed using web platform installer (other downloads from MS documentation don't seem to work)

Set up Azure:
Make a new SQL Database in Azure, take note of the server name, database name, admin login and admin password.  If you already have a server and resource group, automate with Powershell as shown below (you'll need to replace the param values):
New-AzureRmSqlDatabase -DatabaseName "UmbracoDatabase" -ServerName "TheServer" -ResourceGroupName "Whatever" -Edition "Basic"
Add your IP to the Azure SQL db firewall so Umbraco can connect to it later when running locally
Create a new Azure web app, either manually or via Powershell:
New-AzureRmWebApp -ResourceGroupName…