SQL query giving an incorrect syntax error

Select COUNT(*) as 'Number'
From image
WHERE (image.current_phase = 'aggregation' AND (image.raw_filename REGEXP '%gordonpho%back%$'))

The above SQL query is giving me an incorrect syntax error. I want to get the number of rows from the table image where the column image.current_phase has aggregation as text. Also the column image.raw_filename ends with '%gordonpho%back%'.

Can anybody see the syntax error in my开发者_如何学编程 statement?

REGEXP isn't valid in T-SQL, but you don't need it anyway since your "regular expression" would be the same using LIKE anyway:

Select COUNT(*) as 'Number'
From image
WHERE (image.current_phase = 'aggregation'
       AND (image.raw_filename LIKE '%gordonpho%back%')

SQL Server doesn't support regular expressions natively. Try using LIKE:

Select COUNT(*) as 'Number'
From image
WHERE (image.current_phase = 'aggregation' 
    AND (image.raw_filename LIKE '%gordonpho%back%'))

For times where you really need/want to use regular expressions, you'd have to do it via SQLCLR support (.NET code).

It's possible in your SQL implementation that image is a reserved word. Try quoting it with backticks (MySQL), square brackets [] (MSSQL) or double quotes " (most others).

Also, the %s in '%gordonpho%back%$' aren't treated as wildcards in regular expressions. Try replacing the literal with:

'.*gordonpho.*back.*$'