forums

Use these forums to ask questions and discuss Tableau.

Calculated field - UK Postcodes

Posted April 18th, 2008 by jonmkiwi
in Calculations

since there doesn't appear to be any provision for regular expressions in string functions, to convert UK postcodes (e.g. PL4 8RD or B2 34RF) into UK post areas (e.g. PL or B - i.e. the post area can be one or two letters long) I eventually arrived at:

LEFT([postcode],IF ASCII(MID([postcode],2))<58 THEN 1 ELSE 2 END )

(I'd love to think there was an easier way!)

Add new comment

Comments

On April 18th, 2008 Dirk Karis said:

Tableau sends calculated fields to the server rather than calculating them itself. Since regular expressions are not part of the SQL standard, they aren't currently part of the Tableau calculation language.

Certain databases do implement regular expressions, and you can access them using Tableau's support for Pass Through SQL. For instance, this formula would work against a PostgreSQL database:

RAWSQL_STR("substring(%1 from '^[[:alpha:]]{1,2}')", [postcode])

For Oracel 10g, it would be
RAWSQL_STR("REGEXP_SUBSTR(%1, '^[[:alpha:]]{1,2}')", [postcode])

Consult the documentation for your particular database for more information.

reply