Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Well - a number of ways exists depending on which database server you use.

For SQL Server / T-SQL, you can utilize PATINDEX which returns you the placement of a specific string within another string and use that with LEN and SUBSTRING. Something like this:

DECLARE @Name VARCHAR(255) = 'A. Brown'
SELECT SUBSTRING(@Name, PATINDEX('% %', @Name) + 1, LEN(@Name))

I use PATINDEX to find the location of the space, I use LEN to find the length of the string and then I just SUBSTRING it out.

But because often in names there can be multiple spaces, it's often a good trick to reverse the string first and then search for the first space, and then reverse that string again.

So for example your name was A. C. Brown instead, something like this could be done:

DECLARE @Name VARCHAR(255) = 'A. C. Brown'
SELECT REVERSE(SUBSTRING(REVERSE(@Name), 1, PATINDEX('% %', REVERSE(@Name))))

It should then be simple to incorporate into your query logic.

Many approaches exists for such a problem, so basically select what suits you best, but hopefully you'll get some inspiration from this.

There are similar functions in other SQL flavours as well. (but likely with other names).

share|improve this answer
    
And if the name is D. De La Fuente? If the names always are in the pattern '%. last names', then perhaps finding the last '.' would be more reliable. –  RLF Oct 10 '13 at 12:57
    
RLF that will then return all the names with a . at the beginning won't it? –  zatgolf Oct 10 '13 at 13:04
    
The reason I ask is because we have another field called Family Name, and I need to set up a report to compare Family Name with the surname from the Name field as listed above... If the query searches for the . then the returned results will all include .'s and will therefore be different when comparing? –  zatgolf Oct 10 '13 at 13:07
    
Various name conventions of course needs to be addressed. Mine was just an example, not a universal 'name' parse to give inspiration on how to handle such issues. The best way would be that the data was sanitized properly before storage so such logic wouldn't be needed in a query, but that's not - naturally - always possible, and then it's finding what specifically works for a specific situation, such as the question asked in the original post. If a period is always in the name, you can use that instead of a space and just use an additional offset ( + 2 instead of 1 etc) for the SUBSTRING –  Allan S. Hansen Oct 10 '13 at 13:15
    
Thanks Allan, the main issue now is that some of the names are not formatted the way they should be. However the solution you posted works great. Thank you! –  zatgolf Oct 10 '13 at 14:16
add comment

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 < 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> SET @str = 'G. L. Potter';
Query OK, 0 rows affected (0.00 sec)

mysql> 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
*************************** 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>

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> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1);
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1) |
+-----------------------------------------------------+
| L.                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

How about the third token from the right ?

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

mysql>

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.