SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) 
 Jun 12 2020 20:39:00 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE postcode (
  postcode VARCHAR(20) NOT NULL PRIMARY KEY
);

INSERT INTO postcode (postcode) VALUES('SA61 2PY');
INSERT INTO postcode (postcode) VALUES('LE67 1UB');
INSERT INTO postcode (postcode) VALUES('E12 6HS');
INSERT INTO postcode (postcode) VALUES('TN3 0RD');
INSERT INTO postcode (postcode) VALUES('WC2E 9EB');
INSERT INTO postcode (postcode) VALUES('SO32 1LX');
INSERT INTO postcode (postcode) VALUES('SW13 9NA');
INSERT INTO postcode (postcode) VALUES('WR3 8EE');
INSERT INTO postcode (postcode) VALUES('W7 3SA');
INSERT INTO postcode (postcode) VALUES('TW1 4BN');
INSERT INTO postcode (postcode) VALUES('W7 2JN');
INSERT INTO postcode (postcode) VALUES('WR14 4RG');
INSERT INTO postcode (postcode) VALUES('HR9 7LQ');
INSERT INTO postcode (postcode) VALUES('NR12 8YP');
14 rows affected

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END;
ALTER TABLE postcode ADD letters   AS (dbo.fn_StripCharacters(postcode, '^A-Z')) PERSISTED;
ALTER TABLE postcode ADD numbers   AS (CAST(dbo.fn_StripCharacters(postcode, '^0-9') AS INT)) PERSISTED;
CREATE INDEX numbers ON postcode  (numbers DESC)
INSERT INTO postcode (postcode) VALUES('RG40 2NU');
INSERT INTO postcode (postcode) VALUES('UB6 7JB');
INSERT INTO postcode (postcode) VALUES('IV15 9SE');
INSERT INTO postcode (postcode) VALUES('CT18 7QU');
INSERT INTO postcode (postcode) VALUES('WS10 7HB');
INSERT INTO postcode (postcode) VALUES('CA6 5TY');
INSERT INTO postcode (postcode) VALUES('WR1 3PZ');
INSERT INTO postcode (postcode) VALUES('DY8 1XW');
INSERT INTO postcode (postcode) VALUES('SW16 6QR');
INSERT INTO postcode (postcode) VALUES('N5 1FN');
10 rows affected

SELECT * FROM postcode


postcode
letters
numbers


LE67 1UB
LEUB
671

SA61 2PY
SAPY
612

RG40 2NU
RGNU
402

SO32 1LX
SOLX
321

CT18 7QU
CTQU
187

SW16 6QR
SWQR
166

IV15 9SE
IVSE
159

WR14 4RG
WRRG
144

SW13 9NA
SWNA
139

NR12 8YP
NRYP
128

E12 6HS
EHS
126

WS10 7HB
WSHB
107

HR9 7LQ
HRLQ
97

DY8 1XW
DYXW
81

W7 3SA
WSA
73

W7 2JN
WJN
72

UB6 7JB
UBJB
67

CA6 5TY
CATY
65

N5 1FN
NFN
51

WR3 8EE
WREE
38

TN3 0RD
TNRD
30

WC2E 9EB
WCEEB
29

TW1 4BN
TWBN
14

WR1 3PZ
WRPZ
13

postcode	letters	numbers
LE67 1UB	LEUB	671
SA61 2PY	SAPY	612
RG40 2NU	RGNU	402
SO32 1LX	SOLX	321
CT18 7QU	CTQU	187
SW16 6QR	SWQR	166
IV15 9SE	IVSE	159
WR14 4RG	WRRG	144
SW13 9NA	SWNA	139
NR12 8YP	NRYP	128
E12 6HS	EHS	126
WS10 7HB	WSHB	107
HR9 7LQ	HRLQ	97
DY8 1XW	DYXW	81
W7 3SA	WSA	73
W7 2JN	WJN	72
UB6 7JB	UBJB	67
CA6 5TY	CATY	65
N5 1FN	NFN	51
WR3 8EE	WREE	38
TN3 0RD	TNRD	30
WC2E 9EB	WCEEB	29
TW1 4BN	TWBN	14
WR1 3PZ	WRPZ	13