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.
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_nameJoe Bloggs AAJane Doe HHJohn DoeFrom 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 |
|
|
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 queryCASE WHEN ao.officer_name LIKE '% __' and RIGHT(ao.officer_name, 2) in ('pattern1','pattern2','pattern3',...,'patternN') Then RIGHT(ao.officer_name, 2) ELSE NULL ENDCheersMIK |
|
|
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? |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-04 : 10:28:47
|
yup in single qoutesCheersMIK |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-03-04 : 10:37:17
|
Thanks guys! |
|
|
|
|
|
|
|