TSQL getting characters from the rightside of the string using charindex and reverse

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.

Reversed                   Lastname
noraC seluJ yuG imeR  Caron


2 responses to “TSQL getting characters from the rightside of the string using charindex and reverse

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: