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

Gordon Linoff · Accepted Answer · 2018-03-06 12:01:41Z

11

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)

answered Mar 6, 2018 at 12:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luffydude Over a year ago

Oh it kinda worked now but there's still something wrong, my string of WC1B 4HH got trummed into WC1 instead of WC1B. Using your 2nd option it worked fine

kret11 · Accepted Answer · 2023-03-10 22:18:33Z

3

Not only inverted arguments in strpos function, but also the 2nd parameter of left function is the number of characters to pick from the left.

Relating to official postgres docs:

left(str text, n int)

Return first n characters in the string. When n is negative, return all but last |n| characters.

So the correct query would be:

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

answered Mar 10, 2023 at 22:18

kret11

393 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related