I'm having an issue in selecting specific post codes.
These are all valid UK post code formats:
Now, say I had a mix of the above formats in the data table; I'm trying to select only post codes that conform to the WV1 prefix (in this example).
In the above 6 item list, I'd want to return:
I would want to exclude:
If I execute the following query this will bring back both the WV11's and the WV1's:
SELECT ad.PostCode,*
FROM Staff st
INNER JOIN Address ad on ad.AddressID = st.Address
WHERE
ad.PostCode like 'WV1%'
Changing the condition in the WHERE to cater for length like this doesn't really work either:
SELECT ad.PostCode,*
FROM Staff st
INNER JOIN Address ad on ad.AddressID = st.Address
WHERE
(
ad.PostCode like 'WV1%'
OR
(ad.PostCode like 'WV1%' and LEN(ad.PostCode) = 6)
The above will just filter out any of the formats with a space so if we cater for those by doing the below:
SELECT ad.PostCode,*
FROM Staff st
INNER JOIN Address ad on ad.AddressID = st.Address
WHERE
(ad.PostCode like 'WV1%' and LEN(ad.PostCode) = 6)
or
(ad.PostCode like 'WV1 %' and LEN(ad.PostCode) = 7)
That fixes the issue but the problem is that we want to check more than just the 'WV1' prefix in this manner so having a growing list of 'OR' comparisons isn't viable.
How do we isolate the above post codes in a scalable way? I'm using Microsoft SQL Server 2016.
I think the logic you want is:
WHERE ad.PostCode like 'WV1 [0-9][A-Z][A-Z]' OR
ad.PostCode like 'WV1[0-9][A-Z][A-Z]'
I'm not sure if numbers are allowed for the last three characters.