In one of my recent projects i encounterd the following issue with T-SQL string functions. There is no default function to determine a character in a string starting at right of your string. While there are cases in which this might be very handy.
I came across many somewhat difficult solutions to this problem so here is my simple but sufficient solution to this problem.
Lets assume we want to determine the lastname in a fullname string as this one here below:
DECLARE @myvar varchar(30)
SET @myvar = ‘Remi Guy Jules Caron’
It holds all my firstnames and my surname i would like to retrieve only my lastname from the string. The easiest way out would be to start on the right and look for the first space in the string right? Well there the trouble starts you can’t.
Here is my solution to this issue by using REVERSE and CHARINDEX.
SELECT REVERSE(@myvar) AS Reversed, RIGHT(@myVar, CHARINDEX(‘ ‘, REVERSE(@myvar))) as Lastname ;
The first reversed column is just to show what reverse does so isn’t that important. However the seceond column that says lastname does the trick. I used Charindex to determine the space in the string starting from the right by reversing the string with my names in it. Then i took the RIGHT function to actually get the number of characters from the right determined by charindex in the reversed string of names.
noraC seluJ yuG imeR Caron