0

I'm having an issue in selecting specific post codes.

These are all valid UK post code formats:

  1. WV11JX
  2. WV1 1JX
  3. WV102QK
  4. WV10 2QK
  5. WV113KQ
  6. WV11 3KQ

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:

  • WV11JX
  • WV1 1JX

I would want to exclude:

  • WV102QK
  • WV10 2QK
  • WV113KQ
  • WV11 3KQ

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.

  • Worth noting that *technically* a UK postcode is supposed to have a white space before the last 3 characters. So `'WV10JQK'` doesn't fully conform. Wikipedia actually has a [REGEX string](https://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom#Formatting) for validation for them. – Thom A Jan 28 '20 at 12:21
  • Actually, none of those postcodes are valid UK postcodes... UK Postcodes end with `[0-9][A-Z][A-Z]`; all you yours end with `[A-Z][A-Z][A-Z]`. They look more like UK number plates than Postcodes. `'WV11AKQ'` would represent a vehicle constructed in H1 2011 in Bristol. The sector character of a UK Postcode (3rd character from the right/after the white space) is **always** a numerical value. – Thom A Jan 28 '20 at 12:24

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I've corrected the initial 2 here, as the OP has now supplied valid postcodes. Your last one won't work any more, however, Gordon; but it would have based on the OP's original (incorrect) data. – Thom A Jan 28 '20 at 12:34
  • @Larnu chill out with your passive aggressive comments mate.. –  Jan 28 '20 at 13:10
  • 1
    @Sanguine . . . Larnu's comment was helpful and very reasonable. I see nothing negative about it. Larnu also did me the favor of fixing the answer. – Gordon Linoff Jan 28 '20 at 13:12
  • Thanks @GordonLinoff . – Thom A Jan 28 '20 at 14:16