Validating select


In the case of London, some of the densely-populated central districts are further divided by means of an extra letter at the end of the outward code.

For example, within the SW1 district, SW1A covers Whitehall and the Mall, while SW1E is Victoria Station and nearby streets.

Most British overseas territories also have non-standard codes: PCRN 1ZZ is the Pitcairn Islands, for instance.

For most applications, however, these special codes can be ignored.

By Mike Lewis If your database contains UK address data, it's especially important that the postcodes are correctly stored.

British postcodes do much more than speed the mail.

-- Returns invalid postcodes SELECT postcode FROM Addresses WHERE patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', postcode) = 0 and patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', postcode) = 0 and patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', postcode) = 0 As you can see, the query uses T-SQL's PATINDEX() function.