Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get the last two letters only from string?

Author  Topic 

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-04 : 09:07:47
Hi guys,

I want to extract the last two names from officer_name field after a space. E.g.

officer_name
Joe Bloggs AA
Jane Doe HH
John Doe

From the above example, it should only return AA and HH. It should ignore John Doe because he does not have a 2 letter code.

I did:

RIGHT(ao.officer_name, 2) but it doesn't quite stop at the blank space. It returns OE for John Doe.

Is anyone able to share how to do this?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 09:13:40
Here is a way - but this will fail if the name happens to be "John Ho". Even that can be fixed, but then there will be other cases, for example, "John Ho Jr".
SELECT 
CASE
WHEN ao.officer_name LIKE '% [a-zA-Z][a-zA-Z]' THEN RIGHT(ao.officer_name, 2)
ELSE ''
END
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 09:42:17
If the code is of a specific pattern then you can add a condition in the above query

CASE WHEN ao.officer_name LIKE '% __' and RIGHT(ao.officer_name, 2) in ('pattern1','pattern2','pattern3',...,'patternN') Then RIGHT(ao.officer_name, 2) ELSE NULL END

Cheers
MIK
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-04 : 09:53:29
Hi James and MIK thanks!

MIK, when you say pattern is it searching for specific codes? E.g. if I am set list of codes (i.e. AH, BH, ET etc) would go in the IN statement?
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 10:28:47
yup in single qoutes

Cheers
MIK
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-04 : 10:37:17
Thanks guys!
Go to Top of Page
   

- Advertisement -