SQLServer - Select bool if column begins with a string

Question

I would like to select a boolean of whether or not a column begins with a certain string.

SELECT (name LIKE 'foo%') AS isFoo FROM bar;

Is there a way to do this without using an inline CASE?

Solution 1

No

There is neither implicit boolean CAST in SQL Server nor a boolean type

SELECT CAST(CASE WHEN name LIKE 'foo%' THEN 1 ELSE 0 END AS bit) AS isFoo
FROM bar;

Solution 2

You might not even need the cast depending on your usage:

SELECT CAST(PATINDEX('foo%'), name) AS bit) FROM bar

This will return 1 if the col starts with the text otherwise 0. No CASE involved.

Solution 3

A UNION operation would let you skip a CASE statement by combining two result sets. In the first query you filter for all rows that match 'foo%' and in the second you match all rows that do not match 'foo%'

Something like:

SELECT 1 AS [YourBoolean], 'fool' WHERE 'fool' LIKE 'foo%'
UNION
SELECT 0, 'fuel' WHERE 'fuel' NOT LIKE 'foo%' 
ORDER BY 1

(Hard-coded example w/o target table.)

Solution 4

Create a User Defined function that you can call inorder to check if the name contains foo.

Solution 5

Not to take away from what gbn suggested but I think this would be more efficient (but essentially the same thing)

SELECT CAST(CASE WHEN LEFT(name, 3)='foo' THEN 1 ELSE 0 END AS bit) AS isFoo
FROM bar;

View more solutions