[SOLVED] SQL text

[adam84]

is there a way or a function in sql to retrieve only the first 100 words of a column or would I just use php to do that?

[Barand]

You can get the first X characters with the LEFT() function but I don't know of any such word-based MySQL function.

[Wildbug]

You could determine the average number of characters in 100 words in your database, use the LEFT() function to get that many characters, and then use PHP to trim it, but there's no guarantee it'd be 100 words....

[Wildbug]

I just learned something.

 

Behold, the SUBSTRING_INDEX(str,delim,count)function!  "Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned."  Let count be the number of words you are interested in returning, and as long as they are seperated by a space, you'll get that many words.

 

Example:

mysql> SELECT id,title,SUBSTRING_INDEX(content,' ',10) FROM newscontent;
+-----+--------------+------------------------------------------------------------------+
| id  | title        | SUBSTRING_INDEX(content,' ',10)                                  |
+-----+--------------+------------------------------------------------------------------+
| 001 | Title One    | For example, right now, the article that's being displayed fills |
| 002 | Second Title | As you can see, this limits the string length by                 |
| 003 | LENGTH()     | Returns the length of the string str, measured in bytes.         |
+-----+--------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

[adam84]

That awesome thanks

[Barand]

I just learned something.

 

Me too. Missed that one tucked away at the end of the SUBSTRING alternatives in the manual. Good find.