MySQL Query to return rows that contain whitespace

Question

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?

Ignacio Vazquez-Abrams · Accepted Answer · 2011-08-06 22:19:42Z

64

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

answered Aug 6, 2011 at 22:19

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Britic Over a year ago

This didn't work for me, even though there were a couple of rows containing a space.

user823871 · Accepted Answer · 2011-08-06 22:23:40Z

9

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

answered Aug 6, 2011 at 22:23

user823871

Comments

Jose Adrian · Accepted Answer · 2011-08-06 22:20:23Z

1

An idea:

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

answered Aug 6, 2011 at 22:20

Jose Adrian

1,2371 gold badge17 silver badges32 bronze badges

Comments

Brent Matzelle · Accepted Answer · 2020-11-19 21:28:44Z

1

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;

answered Nov 19, 2020 at 21:28

Brent Matzelle

4,1334 gold badges30 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL Query to return rows that contain whitespace

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related