MySQL - Where is the Error in this Query? (Error 1064)-CodePudding

I'm trying to execute the query below but WorkBench keeps complaining about a syntax error (Error 1064). I don't know what the error is because even WorkBench highlights each bracket pair and so I can't say there is a missing bracket. Please help.

SELECT 
If(Right(Trim(`tbloldfurniture`.`NotesOnOldness`), 4) = 'susp', Substring(Trim(`tbloldfurniture`.`NotesOnOldness`), 1, Char_Length(Trim(Lower(`tbloldfurniture`.`NotesOnOldness`)) - 1)) ,Substring(Trim(Lower(`tbloldfurniture`.`NotesOnOldness`))))
FROM `tbloldfurniture`;

This is the same query broken into its separate parts to aid readability.

SELECT
    
    If(

        Right(Trim(`tbloldfurniture`.`NotesOnOldness`), 4) = 'susp', 

        Substring(Trim(`tbloldfurniture`.`NotesOnOldness`), 1, Char_Length(Trim(Lower(`tbloldfurniture`.`NotesOnOldness`)) - 1)) ,

        Substring(Trim(Lower(`tbloldfurniture`.`NotesOnOldness`)))
    )

FROM `tbloldfurniture`;

CodePudding user response：

MySQL's substring() function expects at least two parameters and you're feeding it just one in Substring(Trim(Lower(tbloldfurniture.NotesOnOldness))).

Also, you're probably doing something wrong, as you're deducting 1 from a string in Char_Length(Trim(Lower(tbloldfurniture.NotesOnOldness)) - 1).

And yeah, SQL is notoriously terrible with its error messages. Confusing as heck.

CodePudding user response：

The 1064 error is a syntax error. This means the reason there’s a problem is because MySQL doesn’t understand what you’re asking it to do. Cant see an issue with what you have put tho. If your query attempts to reference information in a database and can’t find it, that could be the issue ?