Query to find words over a certain length without spaces

Hi all,

Hopefully a quick one - I hav a table full of words and phrases and trying to return anything over length 15 without being multiple words (i/e/ no spaces).

So far I have:

SELECT *
FROM `words`
WHERE length( trim( word ) ) >15
LIMIT 0 , 30

Open in new window


But need to exclude phrases with spaces - had a look at REGEXP '[:space:]' but can't seem to get that to work in the query.

So a sudo query:

select * from words where word is longer than 20 but make sure it is a word and not a phrase with spaces

Cheers
dolythgoeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nemws1Connect With a Mentor Database AdministratorCommented:
It looks like some of your words might have trailing/starting spaces that don't concern you.  If so, you're on the right track with TRIM() - just add it before the NOT LIKE:
SELECT *
FROM `words`
WHERE word REGEXP '[a-z]{15}'
  AND TRIM(word) NOT LIKE '% %'
;

Open in new window

0
 
nemws1Database AdministratorCommented:
So if it's a phrase that contains a 15 character word, but there's a space in the field, you don't want it, right?

If so, how about this:
SELECT *
FROM `words`
WHERE word REGEXP '[a-z]{15}'
  AND WORD NOT LIKE '% %'
;

Open in new window

0
 
dolythgoeAuthor Commented:
Nice one! Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.