Returning Right of a string with no specific known length I want to return the data from a column from the right. This would mean using the RIGHT() function, however this means entering a length of what to return. For example, I have the following data:

NAME:

A. Brown
S. Smith
C. Taylor
G. L. Potter
L. M. Lyle


I want to return just the surname, so ideally from the right up to the first space which is a different length with each name. So I want to return Brown, Smith, Taylor, Potter, Lyle.  Im not sure what the best way to do this is... Would substring be better? How would I get that to return from the right too? 

Thanks
If names have blanks separating First, Middle, and Last, you want to use SUBSTRING_INDEX. The function takes three parameters: the string, the delimiter, and the token position. 

If the token position > 0, you collect substring from the left.

If the token position &lt; 0, you collect substring from the right.

Let's pick one of the name from your question : G. L. Potter

Using this query

SET @str = 'G. L. Potter';
SELECT
    SUBSTRING_INDEX(@str,' ',-3) token_3,
    SUBSTRING_INDEX(@str,' ',-2) token_2,
    SUBSTRING_INDEX(@str,' ',-1) token_1,
    SUBSTRING_INDEX(@str,' ', 0) token0,
    SUBSTRING_INDEX(@str,' ', 1) token1,
    SUBSTRING_INDEX(@str,' ', 2) token2,
    SUBSTRING_INDEX(@str,' ', 3) token3
\G


Please note how you can iterate and parse tokens with SUBSTRING_INDEX:

mysql&gt; SET @str = 'G. L. Potter';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT
    -&gt;     SUBSTRING_INDEX(@str,' ',-3) token_3,
    -&gt;     SUBSTRING_INDEX(@str,' ',-2) token_2,
    -&gt;     SUBSTRING_INDEX(@str,' ',-1) token_1,
    -&gt;     SUBSTRING_INDEX(@str,' ', 0) token0,
    -&gt;     SUBSTRING_INDEX(@str,' ', 1) token1,
    -&gt;     SUBSTRING_INDEX(@str,' ', 2) token2,
    -&gt;     SUBSTRING_INDEX(@str,' ', 3) token3
    -&gt; \G
*************************** 1. row ***************************
token_3: G. L. Potter
token_2: L. Potter
token_1: Potter
 token0:
 token1: G.
 token2: G. L.
 token3: G. L. Potter
1 row in set (0.00 sec)

mysql&gt;


If you want the second token from the right, you have to do this:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1);


It runs like this:

mysql&gt; SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1);
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1) |
+-----------------------------------------------------+
| L.                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql&gt;


How about the third token from the right ?

mysql&gt; SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-3),' ',1);
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-3),' ',1) |
+-----------------------------------------------------+
| G.                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql&gt;


You will have to spend some time setting up iterative code around SUBSTRING_INDEX, but at least a parsing mechanism is possible when done as I described.