T-SQL like operator in conditional-CodePudding

I want to do the following (works in MySQL, etc.) in T-SQL:

    IF(name LIKE '%Amazon%', TRUE, FALSE) AS 'is_amazon'

So I tried this, but it errors on the LIKE operator:

   IIF(name LIKE '%Amazon%', 1, 0) AS 'is_amazon'

The error is:

Parse error at line: 25, column: 17: Incorrect syntax near 'LIKE'.

Column 17 in the real world usage is the L in LIKE for what it's worth.

The Microsoft T-SQL documentation says that:

LIKE is a "logical operator" returning a boolean

so why does this not work? How can I make this work in T-SQL?

Server version: Microsoft Azure SQL Data Warehouse - 10.0.15669.0 Jul 14 2022 22:08:00

CodePudding user response：

Here are two options. You can cast as BIT if desired

Declare @YourTable table (SomeCol varchar(50))
Insert Into @YourTable values
 ('Some Name')
,('Some Amazon Vendor')


Select SomeCol
      ,IsAmazon1 = sign(charindex('Amazon',SomeCol))
      ,IsAmazon2 = case when SomeCol like '%Amazon%' then 1 else 0 end
 From  @YourTable

Results

SomeCol             IsAmazon1   IsAmazon2
Some Name           0           0
Some Amazon Vendor  1           1