Is there a shorter more terse way to do a string comparison in SQL Server without wrapping it in a CASE WHEN ... THEN 1 END? In PostgreSQL, I can do

SELECT c, c='foo'
FROM ( VALUES ('foo'),('bar') ) AS t(c);

In SQL Server, I have to do

FROM ( VALUES ('foo'),('bar') ) AS t(c);

In the abstract, is there an easier more SQL-Servery way to throw an expression into boolean context? Or, is there a better way to write strcmp without rolling your own case statement?


2 Answers 2


I'm not sure that you'd call it shorter, but you could use the Logical Function IIF. From the docs,

IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation.

Per the example,

SELECT c, iif(c='foo',1,0)
FROM ( VALUES ('foo'),('bar') ) AS t(c);
  • 2
    Very cool, that's exactly what I was looking for! Though not having true/false defaults implicit is a tragedy. Commented Jul 20, 2018 at 18:39
  • I can understand that Evan, especially after reading your profile :) Thanks for the edit!
    – S3S
    Commented Jul 20, 2018 at 18:39

For fun, here is another way of doing a string comparison to get a binary result out. Just join a table on with the "true" parts of your case and put a 1 for the second column.

SELECT t.c, COALESCE(p.b, 0) AS b
FROM ( VALUES ('foo'),('bar') ) AS t(c)
    LEFT OUTER JOIN (VALUES ('foo', 1)) AS p(c,b) ON p.c = t.c;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.