SQL Trickery – coalesce and nullif

Just a quick post here, wanted to share a cool SQL trick I ran into today as I was authoring a query.

(Yes, this schema is terrible… but the worst schema’s have the best SQL tricks)

So I have a table which contains a string field that I have to do a LIKE partial match…. to TableB.field1, or if TableB.field1 is blank, then match against TableB.field2. ¬†This little beauty did the trick in a jiff!

INNER JOIN ts_Seminars_Details SD
ON SD.URL LIKE  '%' + COALESCE( nullif( ACT.courseKey, '' ), ACT.PK ) + '%'

So 1st we have COALESCE(), which takes an arbitrary amount of arguments, and uses the 1st non-null one.
Then we have nullif(), which takes a value, and tests if its ”, and if it is, returns null (so that our coalesce function will work with a blank string instead of a null in the table)

Cheers

Monday, January 14th, 2013 MS SQL No Comments