Extract character before space from UK postcode

Question

In my data Postcode records are as below

1.'ABC XYZ'
2.' EFG PQR'

Requirements is to get all character before space So for first record ,I am getting expected result if I am putting

select NVL(substr(postcode,0,instr(postcode,' ')-1), postcode)

But for second record I am getting whole postcode value . Because in second record ' '(space is at very beginning).

I tried multiple query but not getting the results .

I want single expression which handles both scenarios.

Answer 1

Try this:

 NVL(substr(ltrim(postcode),0,instr(ltrim(postcode),' ')-1), postcode)

Answer 2

A simple option uses regular expressions:

Sample data:

SQL> with test (id, postcode) as
  2    (select 1, 'ABC XYZ'  from dual union all
  3     select 2, ' EFG PQR' from dual
  4    )

Query:

  5  select id, postcode,
  6    regexp_substr(postcode, '\w+') result
  7  from test;

        ID POSTCODE RESULT
---------- -------- --------
         1 ABC XYZ  ABC
         2  EFG PQR EFG

SQL>