I need to use a query which will pull data from a field when it is less then 4 words, is this possible and if so could you give some pointers? I suppose if not possible it could cycle through the results until it found one which was 3 words and then end the while statement when it has found one.
+10
A:
SELECT column FROM table where LENGTH(column) - LENGTH(REPLACE(column, ' ', ''))+1 < 4
Here Regexp Solution.
select * from TABLE where column not regexp '^.+( .+){4}$'
Luis Melgratti
2009-01-19 13:12:05
This should work. however, you should write that you're checking the original length against the length wit all spaces replaced. It isprobably not obvious to the person asking the question.
Kris
2009-01-19 13:15:48
I think this will fail if the field has leading or trailing spaces
DanSingerman
2009-01-19 13:18:23
This solution is also multibyte safe, the one with regex isn't.
Eineki
2009-01-19 13:18:23
Very clever (as long as space is the only whitespace character that will delimit words in this field).
Chris Simpson
2009-01-19 13:18:48
You can get rid of trailing/leading spaces with a trim
Eineki
2009-01-19 13:19:53
Yes, doing a TRIM on the data will be critical
TravisO
2009-01-19 18:48:04
Keep in mind that per-row functions never scale very well. This is okay for small data sets but not large ones (I define large as those using DB2/z - mainframe DBs with truly enormous numbers of rows so it may not be a problem when using mysql which probably doesn't reach that size).
paxdiablo
2009-01-20 03:41:51
this solution doesn't won't work if there are multiple spaces betwenn some words !
PierrOz
2009-11-18 11:09:32
+1
A:
Calling functions in a select on a per-record basis is almost always a bad idea in terms of performance - disk space is usually much 'cheaper' than CPU capacity (I enclose that in quotes since I'm not talking about dollar costs).
Most databases tend to be "more read than write" as my DBA mentor once told me; the ideal solution in that situation is to have a trigger when inserting or updating the row to calculate the number of words in that column and store them in another column. The reason it's ideal is that the number of words only changes when you update the field, so it's better to perform the time-intensive calculation at that point rather than (unnecessarily) every time you read the record.
The added required space will be minimal since you're adding an (short) integer to a long (string) but you'll find your queries scream along by just using the integer field (make sure you index on it).
paxdiablo
2009-01-19 13:18:14
A:
I was going to suggest a LIKE '% % % %' but I'm not entirely sure if that would work or not.
Joe Philllips
2009-01-19 13:46:56