4

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

SELECT c, CASE WHEN c='foo' THEN 1 END
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?

3

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. – Evan Carroll Jul 20 '18 at 18:39
  • I can understand that Evan, especially after reading your profile :) Thanks for the edit! – scsimon Jul 20 '18 at 18:39
0

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, privacy policy and cookie policy

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