SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get the last two letters only from string?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 03/04/2013 :  09:07:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 03/04/2013 :  09:13:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/04/2013 :  09:42:17  Show Profile  Reply with Quote
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 - 03/04/2013 :  09:53:29  Show Profile  Reply with Quote
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?

Edited by - Maverick_ on 03/04/2013 09:54:36
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/04/2013 :  10:28:47  Show Profile  Reply with Quote
yup in single qoutes

Cheers
MIK
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 03/04/2013 :  10:37:17  Show Profile  Reply with Quote
Thanks guys!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000