Parse last 2 characters of text value if they are letters

post sample values of the text field all possible entries and expected results

There are 32,186 current samples but here are a few examples:

04F13EAM    If the last 2 characters are letters then drop off the 2 letters and put them into a separate field in my query.

result: 04F13E
result for separate field: AM

As long as the last 2 characters to the right are letters ONLY then drop them and then put those 2 letters into a separate field called "SuffixCode".

The balance of the text string can have letters and numbers throughout it but I only want to parse the last 2 characters IF they are both letters. Then drop them off and put them into another field called "SuffixCode". I should also not that the string is NOT always the same amount of characters either. It can be 4 characters long to 20, 30, or even 40 characters long.

Hi,

Pls try like..

Get last 2 characters - RIGHT(ColumnName,2)
Check whether Isnumeric or use LIKE A-Z

SELECT iif( IsNumeric(RIGHT(ColumnName,2)) = True , NULL ,  RIGHT(ColumnName,2) )

OR

SELECT iif( (RIGHT(ColumnName,2)) LIKE "A-Z" ,  RIGHT(ColumnName,2) , NULL )

Hope it helps!

Or like below-

SELECT 
iif
( 

  Instr (1, "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ", RIGHT(ColumnName,1)) > 0
  AND 
  Instr (1, "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ", LEFT(RIGHT(ColumnName,2),1)) > 0
,
  RIGHT(ColumnName,2)
,
  NULL
)

Select allOpen in new window

Hope it helps!

That works sort of. I notice it doesn't work IF the 3 character from the right is also a letter. Which is possible. I need just the last 2 digits regardless of what comes before it as long as the last 2 characters are letters. If they are NOT both letters then I want to return nothing. But I also need to drop those last 2 characters off my string IF they are both letters ONLY. So I need to see the string without those 2 characters as well as those 2 characters in a separate field.

My Example:

04F13EAM    If the last 2 characters are letters then drop off the 2 letters and put them into a separate field in my query.

 result: 04F13E  <---- I need to see it like this without the AM at the end.
 result for separate field: AM <---- And then in another field I need to see the AM

That worked! Thank you very much! I have to post a similar question now because I just realized that this 2 letter code could be in the middle somewhere as well. That's another battle for another time.