Solved

Query to find words over a certain length without spaces

Posted on 2011-09-27
3
240 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:dolythgoe
  • 2
3 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 36712768
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
 
LVL 23

Accepted Solution

by:
nemws1 earned 250 total points
ID: 36712788
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
 

Author Closing Comment

by:dolythgoe
ID: 36713642
Nice one! Thanks a lot
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now