CODEWITHSUNDEEP
sqlsql-server
Albinoswordfish
Albinoswordfish

Reputation: 1957

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?

Upvotes: 0

Views: 2452

Answers (3)

Neil
Neil

Reputation: 3041

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.*$'

Upvotes: -1

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56390

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%')

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147224

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).

Upvotes: 0

Related Questions