in reply to Re^3: Truncate Data from MySQL
in thread Truncate Data from MySQL

SUBSTRING_INDEX() : Return a substring from a string before the specified number of occurrences of the delimiter

And if you look at the actual description you see that positive n (count of the delimiter) returns the string to the left of the delimiter whilst negative n (e.g. -2) return the substring to the right of the nth delimiter. Now image that the delimiter is ' ' (which can be explicitly stated in Mysql as '<space>').

Replies are listed 'Best First'.
Re^5: Truncate Data from MySQL
by zebedee (Pilgrim) on Jul 08, 2009 at 07:53 UTC
    And here is an example:

    mysql> SELECT SUBSTRING_INDEX('A bunch of words that you only want the
+ first fifteen from goes here as this example shows nicely',' ',15) A
+S first_15 FROM property LIMIT 1;
+---------------------------------------------------------------------
+----+
| first_15                                                            
+    |
+---------------------------------------------------------------------
+----+
| A bunch of words that you only want the first fifteen from goes here
+ as | 
+---------------------------------------------------------------------
+----+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX('But what if only a few words?',' ',15) 
+AS first_15 FROM property LIMIT 1;
+-------------------------------+
| first_15                      |
+-------------------------------+
| But what if only a few words? | 
+-------------------------------+
1 row in set (0.00 sec)

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom