MySQL Query to return rows that contain whitespace

user_872614 0
Viewed 45743

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?

mysql sql
4 Answers

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;
user_3301100
Related