Create some sample data:

IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL

DROP TABLE #SampleData

CREATE TABLE #SampleData (

[ID] INT IDENTITY(1,1) NOT NULL,

[strVal] NVARCHAR(50) NULL,

PRIMARY KEY (ID))

INSERT INTO #SampleData

VALUES

('123453ME'),('124344'),('323390'),('32339826455MS'),

('345678'),('39WA'),('986545'),('1234AK'),

('345XX8'),('398644GA'),('F986545'),('98234AZ')

Now query the data and strip off the unwanted suffixes

as well as eliminating any entries with an embedded alpha char.

SELECT

r.strVal

FROM

(

SELECT

(CASE

WHEN PATINDEX('%[A-Za-z]%',RIGHT(strVal,2)) > 0

THEN REPLACE(strVal,RIGHT(strVal,2),'')

ELSE strVal

END) AS strVal

FROM

#SampleData AS sd

) r

WHERE

PATINDEX('%[A-Za-z]%',strVal) = 0