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 ) + '%'
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