CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Konrad
Konrad

Reputation: 1

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.

SELECT PostCode
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?

Thanks!

EDIT:

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.

Upvotes: 0

Views: 132

Answers (3)

Andomar
Andomar

Reputation: 238116

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]')

Upvotes: 1

Serg
Serg

Reputation: 22811

Non-digit followed by 1 digit ... LIKE '%[^0-9][0-9]'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If the issue is that you are getting values with multiple digits and you are using SQL Server (as suggested by the syntax), then you can do:

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

Or, if you know there are at least two characters, you could use:

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

Upvotes: 1

Related Questions