Matas Vaitkevicius Matas Vaitkevicius - 1 year ago 201
SQL Question

How to know where to split UK postcode?

Some genius in some of our

tables has split UK postcode in two columns (
) and in some it's in one column (normal
). Tables with single column are polluted with postcodes with and without
' '
(space) in between (some like
and some like
CT20 1AZ
). I need to search by these postcodes.

In UI both postcodes with and without space are accepted, and this is a requirement.

When code entered with space I am just splitting them on space for searches in 2 column scenario and for single column I look for both with and without space (
), easy-peasy.

However when postcode in UI is entered without space I need to know where I am supposed to split it as I need to search for first part (
) in 2 column situation and to add space in-between to look in one column situation in case postcode was entered with space.

(Fixing db is not an option obviously), so I was thinking could this be done by regex to capture groups and then parse them this way, (I am open to non regex hacky solutions too).

How to know where to split UK postcode?

Answer Source

Don't split the user postcode up - join up the postcode in the db by removing spaces.

You can do this either in a WHERE clause:

SELECT * from Addresses WHERE replace(postcode, ' ', '') = @postcode

SELECT * from Addresses2 WHERE areacode + streetcode = @postcode

Or better if possible, create an indexed computed column

ALTER TABLE Addresses ADD PostcodeLookup AS (replace(postcode, ' ', '')) 

ALTER TABLE Addresses2 ADD PostcodeLookup AS (areacode + streetcode) 

SELECT * from Addresses WHERE PostcodeLookup = @postcode

You should also remove spaces from the user input.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download