How to get all the string before an empty space in postgres?

Question

So basically I need to get a part of the postcode string, all before an empty space

my current query is

select postcode, left(postcode, length(postcode) - strpos(' ', postcode)) 
from postcodetable

But it just doesn't return the post code correctly, example: 1st column is NW1 1AA, 2nd column should just be NW1 but instead it just repeats the first column

Answer 1

Your arguments to strpos() are in the wrong order. So you can do:

select postcode, left(postcode, length(postcode) - strpos(postcode, ' '))
from (values ('NW1 1AA')) v(postcode);

You can also do this using substring() with regular expressions:

select postcode, substring(postcode from '[^ ]+'::text)