Matas Vaitkevicius Matas Vaitkevicius - 5 months ago 45
SQL Question

How to know where to split UK postcode?

Some genius in some of our

MSSQL
and
Oracle
tables has split UK postcode in two columns (
areacode
,
streetcode
) and in some it's in one column (normal
postcode
). Tables with single column are polluted with postcodes with and without
' '
(space) in between (some like
CT201AZ
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 (
CT201AZ OR CT20 1AZ
), 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 (
areacode
) 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

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.