MySQL Query to return rows that contain whitespace

I would like to run a query on a MySQL database table to return all the rows where the column data has white space. e.g.:

Usernames

andy davies

peter

geoff smith

steve

bob paul

The query would开发者_运维问答 only return 'andy davies', 'geoff smith', 'bob paul' and ignore 'peter' and 'steve'

Any ideas?

SELECT ...
   ...
  WHERE username LIKE '% %'

INSTR() returns the position of the specified substring in the given string. Use it in the WHERE conditional.

SELECT * FROM table WHERE INSTR(`username`, ' ') > 0

An idea:

SELECT names FROM tbl_name WHERE names LIKE "% %";

This version checks for all white space including tabs and new lines (it assumes the column is username):

SELECT
  username,
  REPLACE(REPLACE(REPLACE(username, ' ', ''), '\t', ''), '\n', '') AS username_clean
FROM [your_table]
HAVING username <> username_clean;