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,}';
3 Answers
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.
answered Sep 17, 2015 at 12:27
Wiktor Stribiżew
631k41 gold badges502 silver badges633 bronze badges
Comments
Your POSIX class must be,
SELECT * FROM book WHERE titles REGEXP '[[:space:]]{2,}';
No need for ,
SELECT * FROM book WHERE titles REGEXP '[[:space:]]{2}';
You may also use [[:blank:]]
SELECT * FROM book WHERE titles REGEXP '[[:blank:]]{2}';
4 Comments
sotirios
sorry, I must find 2 spaces or more. I am going to edit the question. sorry again
Avinash Raj
ya, atleast two spaces.. My regex should satisfy the rules.
Musa Haidari
This finds exactly two: '[[:space:]]{2}'
Avinash Raj
@Musa atleast two or more. So exactly 2 comes within atleast two.
If you mean just the space character: REGEXP ' '. Or you could use LIKE "% %", which would be faster. (Note: there are 2 blanks in those.)
Otherwise, see http://dev.mysql.com/doc/refman/5.6/en/regexp.html for blank and space.
SELECT * FROM book WHERE titles LIKE '%<two_spaces>%'. Since the 2 spaces already meet your condition, you really do not need to check if there are more than 2. I have just tried that and it seems to work. If you need to match a regular ASCII space (dec. code 32), you do not need a REGEXP.LIKEis more efficient? Unless you need to match any whitespace, I'd choose a LIKE-based solution.