Search code examples
sqlamazon-redshiftpostal-code

UK Postcode area in Redshift

Im trying to extract the first part of a UK postcode using SQL (Redshift) For example, BT28 8YT should return BT - so basically the first one or two alphabetical characters, but it should not return a number. I'm finding the UK Govt regex a little tough to work with!

Heres what I have so far but I'm struggling with what regex function I should be using, so redshift is throwing syntax errors.

[Amazon]() Invalid operation: syntax error at or near "REGEXP";

SELECT 
    IF((left(orders.customer_postcode,2) REGEXP '[0-9]') = 0, left(orders.customer_postcode,2), left(orders.customer_postcode,1)) AS "orders.postcode_area"
FROM orders
Solution

  • The postcode area will always be one or two letters followed by a number.

    It looks like you can use REGEXP_SUBSTR to do this:

    http://docs.aws.amazon.com/redshift/latest/dg/REGEXP_SUBSTR.html

    select regexp_substr(customer_postcode,'^[a-zA-Z]{1,2}') from orders