-1

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.

ahmed
  • 9,071
  • 3
  • 9
  • 22
Padawan
  • 313
  • 5
  • 21

2 Answers2

2

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]

Sean Conkie
  • 127
  • 1
  • 8
  • Thank you so much, but it's not recognizing that function. Any idea what it would be for SQL Server? – Padawan Dec 29 '22 at 21:31
  • 1
    @Padawan - as far as I can see T-SQL has no `regexp_replace` and doesn't really support regular expressions outside of `LIKE` conditions. There are some alternatives such as [this similar question](https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server?noredirect=1&lq=1). Seems like you need to create a function or recursive query to validate each character yourself - this approach would however allow you to skip some of the steps and just start at the last character and work backwards until you found your pair of letters. – Sean Conkie Dec 29 '22 at 21:43
2

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

ahmed
  • 9,071
  • 3
  • 9
  • 22