REGEXP Error - An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'

3
votes

I am currently learning SQL and using SSMS 2017. I can't understand why I am getting an error when using the REGEXP syntax that it seems to work for anyone else:

SELECT *
FROM List
WHERE Name REGEXP '^.[a-e].*'
ORDER BY Name;

Error: An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'.

1
sqlsql-server
Presumably you are learning SQL Server using SMSS, and SQL Server does not have built-in support for regular expressions. – Gordon Linoff
SSMS can only work with SQL Server - not with MySQL – a_horse_with_no_name

1 Answers

4
votes

Your error is a SQL Server error, not a MySQL error. SMSS is a Microsoft product, usually used with SQL Server. Hence, I conclude that you are learning SQL Server.

SQL Server does not support the REGEXP operator. But it does extend the LIKE pattern matching. You can do what you want as:

SELECT l.*
FROM List l
WHERE l.Name LIKE '%[a-e]%'
ORDER BY Name;

It is also worth noting that your REGEXP pattern is more complicated than necessary. Regular expressions match anywhere in the string, so this finds any names that have the letters "a" through "e":

    WHERE Name REGEXP '[a-e]'