SQL Query where a field value does not contain empty spaces

Question

I am currently using the follow query:

SELECT *
FROM `wp_usermeta`
WHERE meta_key='avatar'
AND meta_key NOT LIKE '% '
ORDER BY RAND()
LIMIT 4

In that way, I want to try to get only field values, where no empty spaces re in the file name. Where is the error in my query? It still selects filenames with empty spaces in the filename.

Community · Accepted Answer · 2017-05-23 12:00:50Z

15

Try

NOT LIKE '% %'

Your current wildcard match only catches trailing spaces.

Also, you're using meta_key twice. Should the column used in your LIKE clause be meta_value (or whatever it is in Wordpress).

This question is probably worth reading if you're concerned about performance - Which is faster — INSTR or LIKE?

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Feb 16, 2011 at 22:17

Phil

166k25 gold badges265 silver badges269 bronze badges

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

3 Comments

Lars Over a year ago

yeah..probably the second meta_key was wrong...ist should be meta value. I tested it before with % %. Thanks for your help!

Lars Over a year ago

okay. I read it and decided for this way: 'SELECT * FROM wp_usermeta WHERE meta_key='avatar' AND NOT INSTR(meta_value,'% %') ORDER BY RAND() LIMIT 4' works fine!

Phil Over a year ago

@Lars You don't need wildcards in INSTR. Also, I'd steer clear of automatic boolean conversion. Be specific, use AND INSTR(meta_value, ' ') = 0. Don't know why you're changing from the NOT LIKE query as the performance is the same as INSTR and the query would be easier to understand

Collectives™ on Stack Overflow

SQL Query where a field value does not contain empty spaces

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related