Please can someone help me with SQL query how to get maximum number from an alphanumeric field. Below is the column values. As you can see I want to retrieve the highest number as OSS120 in the below values. The first 3 letters are always constant. Thanks in advance.
The below query retrieved OSS7 instead of OSS120 as the highest number in the list.
Code:
select MailRefNo from MailRegistry
where REPLACE(MailRefNo,'[A-Za-z]','')=
(select max(REPLACE(MailRefNo,'[A_Za-z]',''))
from MailRegistry)
ASG1
ASG10
ASG14
ASG2
ASG21
ASG4
LTS1
LTS10
LTS14
LTS2
LTS21
LTS4
OSS1
OSS10
OSS100
OSS11
OSS114
OSS120
OSS2
OSS21
OSS3
OSS4
OSS5
OSS6
OSS7