Oracle Apex get first part of Postcode

0
votes

Oracle 11g

APEX 4.2.6.00.03

I have Postcode field in a form. eg. (L10 1TY or WF2 5TG or W7 5RR)

I also have a region look up table from which gives me a region

eg.

  • L = Region 1
  • WF = Region 2
  • W = Region 3

How would I get the first part of the Postcode so I could lookup the value against the region table.

1
oracleoracle11goracle-apex

1 Answers

2
votes

You can use a regular expression to get the first substring that just has characters, something like regexp_substr(postcode, '^[[:alpha:]]+')

For example:

with t as (
  select 'L10 1TY' as postcode from dual
  union all select 'WF2 5TG' from dual
  union all select 'W7 5RR' from dual
)
select postcode, regexp_substr(postcode, '^[[:alpha:]]+', 1, 1) as region
from t;

POSTCODE REGION 
-------- -------
L10 1TY  L       
WF2 5TG  WF      
W7 5RR   W

Read more about the regexp_substr() function and Oracle's support regular expressions in the manuals.