How to Get the Second Sequence of Characters using Regular Expressions

Question

I have a field with an address in it (ex. 68 TIDAL BREEZE DR) and this regular expression gets everything before the last sequence (ex. 68 TIDAL BREEZE):

substring (address from '(.*) ')

My question is, how do I modify this expression to get everything after the first sequence (ex. 68) and everything before the last sequence (ex. DR) like so: TIDAL BREEZE?

I'm using PostgreSQL 9.5.

Answer 1

postgres=# select substring('68 TIDAL BREEZE DR' from '\s+(.*)\s');
  substring
--------------
 TIDAL BREEZE
(1 row)

Lazy match the first whitespace character to chop the first bit off.

Answer 2

Not using a regex

I highly suggest you don't parse addresses with a regex. If you're looking for a better practice, there is a method of parsing addresses demonstrated and explained in this answer

SELECT * FROM standardize_address(
  'us_lex','us_gaz','us_rules',
  '68 TIDAL BREEZE DR, Citytown Texas, 77346'
);
 building | house_num | predir | qual | pretype |     name     | suftype | sufdir | ruralroute | extra |   city   | state | country | postcode | box | unit 
----------+-----------+--------+------+---------+--------------+---------+--------+------------+-------+----------+-------+---------+----------+-----+------
          | 68        |        |      |         | TIDAL BREEZE | DRIVE   |        |            |       | CITYTOWN | TEXAS | USA     | 77346    |     | 
(1 row)

Or to get just certain values

SELECT house_num || ' Other Stuff ' || suftype AS whatever
FROM standardize_address('us_lex', 'us_gaz', 'us_rules',
  '68 TIDAL BREEZE DR, Citytown Texas, 77346'
);
       whatever       
----------------------
 68 Other Stuff DRIVE
(1 row)