sotirios sotirios - 1 year ago 45
SQL Question

MySQL regex matching rows containing two or more spaces in a row

I am trying to write a MySQL statement which finds and returns the book registrations that contain 2 or more spaces in a row.

The statement below is wrong.

SELECT * FROM book WHERE titles REGEXP '[:space]{2,}';


Since the 2 spaces already meet your condition, you really do not need to check if there are more than 2. Moreover, if you need to match a regular ASCII space (decimal code 32), you do not need a REGEXP operator, you can safely use

SELECT * FROM book WHERE titles LIKE '%  %';

LIKE is preferred in all cases where you can use it instead of REGEXP (see MySQL | REGEXP VS Like)

When you need to match numerous whitespace symbols, you can use WHERE titles REGEXP '[[:space:]]{2}' (it will match [ \t\r\n\v\f]), and if you only plan to match tabs and spaces, use WHERE titles REGEXP '[[:blank:]]{2}'. For more details, see POSIX Bracket Expressions.

Note that [:class_name:] should only be used inside a character class (i.e. inside another pair of [...], otherwise, they are not recognized.