More elegant way of stripping spaces from a postcode?

Database

More elegant way of stripping spaces from a postcode?

Hi All

I've put together a select statement that strips spaces from UK postcodes, which can be either 5, 6 or 7 characters (without spaces). We have data issues here, where the number of spaces between a postcode can either be 0,1,2 or 3 (depending on which system has populated a postcode). Obviously cleaning up the source data is the long term measure, but in the interim i've come up with the following:

SEL
case when (substring(POSTCODE from 3 for 3) = ' ')
THEN trim(substring(POSTCODE from 1 for 2)||substring(POSTCODE from 6 for 3))
ELSE case when (substring(POSTCODE from 3 for 2) = ' ')
THEN trim(substring(POSTCODE from 1 for 2)||substring(POSTCODE from 5 for 3))
ELSE case when (substring(POSTCODE from 3 for 1) = ' ')
THEN trim(substring(POSTCODE from 1 for 2)||substring(POSTCODE from 4 for 3))
ELSE case when (substring(POSTCODE from 4 for 3) = ' ')
THEN trim(substring(POSTCODE from 1 for 3)||substring(POSTCODE from 7 for 4))
ELSE case when (substring(POSTCODE from 4 for 2) = ' ')
THEN trim(substring(POSTCODE from 1 for 3)||substring(POSTCODE from 6 for 4))
ELSE case when (substring(POSTCODE from 4 for 1) = ' ')
THEN trim(substring(POSTCODE from 1 for 3)||substring(POSTCODE from 5 for 4))
ELSE case when (substring(POSTCODE from 5 for 3) = ' ')
THEN trim(substring(POSTCODE from 1 for 4)||substring(POSTCODE from 8 for 4))
ELSE case when (substring(POSTCODE from 5 for 2) = ' ')
THEN trim(substring(POSTCODE from 1 for 4)||substring(POSTCODE from 7 for 4))
ELSE case when (substring(POSTCODE from 5 for 1) = ' ')
THEN trim(substring(POSTCODE from 1 for 4)||substring(POSTCODE from 6 for 4))
ELSE trim(POSTCODE) END END END END END END END END END
AS POSTCODE_2 from JG_POSTCODE_TEST t1

My question is, is there a more elegant way of doing this? The above statement seems a bit long winded for what seems to me to be a simple operation.

Kind regards,

James
2 REPLIES

Re: More elegant way of stripping spaces from a postcode?

Since Teradata version 12 doesn't have regular expressions available and they won't let me use a UDF, I created a Talend ETL job that pulls the data out of Teradata cleans with a regular expression and then re-inserts the data back into Teradata.

Your Talend job would look like:
tTeradataInput > tMap > tTeradataOutput

Use specify the regular expression in the tMap component:
row1.POSTCODE.replaceAll("[^0-9]","").trim()

Talend Open Studio:
http://www.talend.com/demo/#di

Re: More elegant way of stripping spaces from a postcode?

If you can use UDF's, there is a UDF available that does the equivalent of a "Replace" in Oracle. It's called OReplace

So, if you implemented that UDF, you could simply say:

SELECT OReplace(POSTCODE, ' ', '')