Extract character before space from UK postcode-CodePudding

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.

CodePudding user response：

Try this:

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

CodePudding user response：

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>