Pythonn00b Pythonn00b - 3 months ago 27
SQL Question

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

Answer

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
Comments