Get Rightmost Pair of Letters from String in SQL-CodePudding

Given a field with combinations of letters and numbers, is there a way to get the last (Rightmost) pair of letters (2 letters) in SQL?

SAMPLE DATA
RT34-92837DF82982
DRE3-9292928373DO

FOR THOSE, I would want DF and DO

For clarity, there will only be numbers after these letters.

Edits

This is for SQL Server.

CodePudding user response：

I would remove any characters that aren't letters, using REGEXP_REPLACE or similar function based on your DBMS.

regexp_replace(col1, '[^a-zA-Z] ', '')

Then use a RIGHT or SUBSTRING function to select the "right-most".

right(regexp_replace(col1, '[^a-zA-Z] ', ''), 2)
substring(regexp_replace(col1, '[^a-zA-Z] ', ''),len(regexp_replace(col1, '[^a-zA-Z] ', ''))-2,len(regexp_replace(col1, '[^a-zA-Z] ', ''))

If you can have single occurrences of letters ('DF1234A124') then could change the regex pattern to remove those also - ([^a-zA-Z][a-zA-Z][^a-zA-Z])|[^a-zA-Z]

CodePudding user response：

As you said, there will only be numbers after these letters, you can use the Trim and Right functions as the following:

select 
  Right(Trim('0123456789' from val), 2) as res
from t

Note: This is valid from SQL Server 2017.

For older versions try the following:

select 
  Left
  (
    Right(val, PATINDEX('%[A-Z]%', Reverse(val)) 1),
    2
  ) as res
from t

See demo