The sample customer matches two rows in the postcodes table:
SELECT
c.SHORT_POSTCODE,
c1.SHORT_POSTCODESquish,
x.StartPostCodeSquish,
x.EndPostCodeSquish,
CASE WHEN c1.SHORT_POSTCODESquish BETWEEN x.StartPostCodeSquish AND x.EndPostCodeSquish THEN 'Match' ELSE '' END
FROM h_Customer c
CROSS APPLY (SELECT SHORT_POSTCODESquish = REPLACE(c.SHORT_POSTCODE,' ','')) c1
CROSS APPLY (
SELECT
StartPostCodeSquish = REPLACE(StartPostCode,' ',''),
EndPostCodeSquish = REPLACE(EndPostCode,' ',''),
trfzn
from st_StobartPostCode
) x
ORDER BY x.StartPostCodeSquish
Does this help any?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden