SQL LIKE condition working with result from another query

Question

Does something like this work

SELECT color
FROM rawdata 
WHERE color LIKE (
    SELECT TOP 1 color_condition 
    FROM rules
    WHERE rules_id=1
)

If the color_condition of rule 1 would be B% would this return an entry from the rawdata where color = 'Blue'?

Answer 1

Yes.

At least in SQL Server it does and it looks like you are using that from the TOP

WITH rawdata(color) As
(
SELECT 'Blue' UNION ALL SELECT 'Red'
), rules(color_condition,rules_id) AS
(
SELECT 'B%',1
)
SELECT color FROM rawdata 
WHERE color LIKE (SELECT TOP 1 color_condition 
                  FROM rules WHERE rules_id=1)

Returns

color
-----
Blue