So one of the things I have to do a lot in my current job is build web pages in SQL Server. SQL is not normally what you use to build web pages, but it is what is available to us. So recently on a blog I saw reference to the CONCAT string function in SQL, which has the added bonus of essentially skipping NULL values (treats it as an empty string). So many times I’ve run into the “WHERE DID MY WEB PAGE GO?!?” because when I did a building up my web page, varchar by varchar, something I didn’t expect to ever have a NULL value, did.
So my code can go from (imagine there is HTML in the ” below, it wants to hide code, I’ll have to figure it out at some point):
select @html = @html + '' from mytable ... '+coalesce(val1,'') + ' ' + coalesce(val2,'') + '
to
select @html = CONCAT(@html, '') from mytable ... ', val1, ' ', val2, '
and never get hit by the NULL again when I forgot to put in a coalesce.
Unfortunately, it looks to be only available in SQL Server 2012 and later, and for our main server at work we’re still using SQL 2008 R2. I may need to push for an upgrade …