Let's say we have a table of records as under
Record
------
01A1
02A1
18A1
20A1
17A1
19A1
01B1
02B1
20B1
17B1
18B1
19B1
We need the extract only the alphabets such that the final output should appear as
Record Alphabets
----- ---------
01A1 A
02A1 A
18A1 A
20A1 A
17A1 A
19A1 A
01B1 B
02B1 B
20B1 B
17B1 B
18B1 B
19B1 B
The below query will do so
DECLARE @T TABLE(Record VARCHAR(20))
INSERT INTO @T
SELECT '01A1' UNION ALL
SELECT '02A1' UNION ALL
SELECT '18A1' UNION ALL
SELECT '20A1' UNION ALL
SELECT '17A1' UNION ALL
SELECT '19A1' UNION ALL
SELECT '01B1' UNION ALL
SELECT '02B1' UNION ALL
SELECT '20B1' UNION ALL
SELECT '17B1' UNION ALL
SELECT '18B1' UNION ALL
SELECT '19B1'
SELECT
Record
,Alphabets = LEFT(REVERSE(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 2)),1)
FROM @T