CODEWITHSUNDEEP
sqlregexdatabasestringpostgresql
Matt
Matt

Reputation: 1119

Retrieve Second to Last Word in PostgreSQL

I am using PostgreSQL 9.5.1

I have an address field where I am trying to extract the street type (AVE, RD, ST, etc). Some of them are formatted like this: 5th AVE N or PEE DEE RD N

I have seen a few methods in PostgreSQL to count segments from the left based on spaces i.e. split_part(name, ' ', 3), but I can't seem to find any built-in functions or regular expression examples where I can count the characters from the right.

My idea for moving forward is something along these lines:

select case when regexp_replace(name, '^.* ', '') = 'N'
            then *grab the second to last group of string values*
       end as type;

Upvotes: 1

Views: 1304

Answers (1)

rd_nielsen
rd_nielsen

Reputation: 2459

Leaving aside the issue of robustness of this approach when applied to address data, you can extract the penultimate space-delimited substring in a string like this:

with a as (
    select string_to_array('5th AVE N', ' ') as addr
    )
select
    addr[array_length(addr, 1)-1] as street
from
    a;

Upvotes: 2

Related Questions