Need correct sql command to delete where CHAR_LENGTH() more than

I need the correct SQL command to delete where CHAR_LENGTH() more than x amount I tried

TRUNCATE * FROM foo WHERE CHAR_LENGTH(foobar) > 100

(also tried DELETE *)

which according to w3cshools is a valid operator but it just comes back as error:

> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' FROM foo WHERE CHAR_LENGTH(foobar) > 100' at line 1*

Note: The error message actually shows > as ASCII "& gt;" (no space or quotes) not sure if that has any relevance ...

I also tried:

SELECT * FROM foo WHERE CHAR_LENGTH(foobar) > 100

Just to see if that would do anything and it did what was expected and showed all with a charcount greater then 100.

As always any help is开发者_运维百科 appreciated.

I think you are looking for the DELETE operation, not TRUNCATE.

DELETE FROM foo WHERE CHAR_LENGTH(foobar) > 100;

TRUNCATE have this syntax:

TRUNCATE foo;

and empties the table completely. You cannot have a WHERE clause on TRUNCATE.

You can't use TRUNCATE TABLE with a where clause (Ref.)

Use DELETE instead:

DELETE FROM foo WHERE CHAR_LENGTH(foobar) > 100