Konrad Konrad - 1 year ago 60
SQL Question

Returning postcodes (varchars) with only one numeric character in them

I've been asked to run a query to return a list of UK post codes from a table full of filters for email reports which only have 1 number at the end. The problem is that UK post codes are of variable length; some are structured 'AA#' or 'AA##' and some are structured 'A#' or 'A##'. I only want those that are either 'AA#' or 'A#'.

I tried running the below SQL, using length and (attempting to) use regex to filter out all results which didn't match what I wanted, but I'm very new to using ranges and it hasn't worked.

FROM ReportFilterTable RFT
WHERE RFT.FilterType = 'Postcode'
AND LEN(RFT.Postcode) < 4
AND RFT.PostCode LIKE '%[0-9]'

I think the way I'm approaching this is flawed, but I'm clueless as to a better way. Could anyone help me out?



Since I helpfully didn't include any example data originally, I've now done so below.

This is a sample of the kind of values in the column I'm returning, with examples of what I need to return and what I don't.

  • B1 -- Should be returned

  • B10 -- Should not be returned

  • B2 -- Should be returned

  • B20 -- Should not be returned

  • B3 -- Should be returned

  • B30 -- Should not be returned

  • SE1 -- Should be returned

  • SE10 -- Should not be returned

Answer Source

You could filter for one or two letters (and omit the length check, since it's implicit in the LIKE):

WHERE RFT.FilterType = 'Postcode' AND
      (RFT.PostCode LIKE '[A-Z][0-9]' OR RFT.PostCode LIKE '[A-Z][A-Z][0-9]')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download