Sub String a UK Postcode in a script

[stuwannop]

Hi Everyone

I have a geocodes table that I want to join up to a customer table (which has a UK postcode). My goecode table only contains the first half of theUK postcode (i.e. BB5 or AB24). In my script I need to substring the Postcode field in my customer table. Does someone know how to do this?

I need to bear in mind that I don't always need tyhe first x characters as string I need could be anything from 2 to 4 characters long.

Thanks for any help.

Stu

[swuehl]

Try something like

left( Postcode, ceil(len(Postcode)/2) ) as PostCodeGeoCodeMatch,

instead of ceil() function, you might need to use floor() depending on how the "first half" of Postcodes in the geocode table is determined when the number of characters is uneven.

Alternatively, you could try using one of the match functions or a mapsubstring together with a lookup table (try using a lookup table with two times the geocode's postcodes field).

Hope this helps,

Stefan

[swuehl · Accepted solution]

Ah, UK postcodes have a space as delimiter, right? And you need the first part only?

subfield( Postcode, ' ', 1) as PostCodeGeoCodeMatch

[stuwannop]

Thanks Stefan

It looks like both the solutions you offered work. I went with the first option

left( Postcode, ceil(len(Postcode)/2) )) as PostCodeGeoCodeMatch

Now just need to work out google maps!

Help much appreciated.

Stu