5

Why does the query:

SELECT CAST((column LIKE '%string%') AS INT)+100

return

Incorrect syntax near the keyword 'AS'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mansoor
  • 285
  • 2
  • 8
  • 19
  • 2
    What are you trying to achieve? You can use that expression in a `WHERE` clause but no in a `CAST`. – Tim Schmelter Feb 24 '13 at 20:57
  • SQL Server supports the [bit](http://msdn.microsoft.com/en-us/library/ms177603) data type. (Valid values are 0, 1, 'TRUE' and 'FALSE'.) There is a [boolean](http://msdn.microsoft.com/en-us/library/ms188074.aspx) data type (with values TRUE, FALSE and UNKNOWN), but you cannot get a firm grip on one. – HABO Feb 24 '13 at 21:15

2 Answers2

9

Because bool is not a type in T-SQL. It does not exist. Boolean expressions are not of type bit. They don't have a type - they are only allowed if allowed by the grammar in special places. And yes, this is awful.

SELECT (case when (column LIKE '%string%') then 1 else 0 end)+100
usr
  • 168,620
  • 35
  • 240
  • 369
  • Right, copy n paste error. I hope the idea came across though. You can still cast, if you want. It just doesn't help. – usr Feb 24 '13 at 21:17
0

CAST does not evaluate expressions. And any way, how would you case a boolean result to an Int? That would never work. @usr's answer is the correct way to go.

Ofer Zelig
  • 17,068
  • 9
  • 59
  • 93
  • Why shouldn't that work? Other languages are pretty fine with and at very least a cast might be seen as syntactic sugar for accepted solution. It's just not supported and that's it. Late reply, I know; just explaining my down-vote... – Aconcagua Sep 22 '21 at 14:39