Extract string from text field in PostgreSQL (using regex?)

Question

I have a lot of rows with city and direction fields. But from an old import, city and direction are mixed in direction field. Like:

dir number,  extra data, CITY,  AL 111111
dir number, CITY,  AL 111111
number, dir, number, CITY, dir number, CITY,  AL 111111

The important thing is that 'CITY' always comes before a US postal code and I want to extract it and save it in city field with an UPDATE (using regex?). Is it possible?

Something like:

update TABLE set city = SOME_REGEX_MAGIC_FROM_DIRECTION_FIELD
where direccion ~ 'REGEX_MAGIC'

The working SQL statement:

update TABLE
set city = substring(direction FROM '([^,]+),[^,]+$')
where direction like '%,  __ _____';

Erwin Brandstetter · Accepted Answer · 2018-12-20 22:27:11Z

3

If you want the part before the last comma, one way (of many) is with a plain substring() call (regexp variant):

substring(direction FROM ',([^,]+),[^,]+$') AS city

db<>fiddle here

Your UPDATE statement might look like this:

UPDATE tbl
SET    city = substring(direction FROM ',([^,]+),[^,]+$')
WHERE  direction ~ ', *\D\D \d{5}$'

edited Dec 20, 2018 at 22:27

answered Apr 19, 2014 at 13:16

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Indeax Over a year ago

Thanks! Excuse my english if I didn't explained well. Your regex work great. I always forget about negation in regex and it solved a lot of problems :) I also saw there is another records like "city, XX 99999", with a small change to your regex it work like a charm. The working SQL statement: update TABLE set city = substring(direction FROM '([^,]+),[^,]+$') where direction like '%, __ _____' 102051 registers changed :)

Wolph · Accepted Answer · 2014-04-19 11:04:47Z

0

From your data I would gather that you'll need something like this:

SELECT regexp_matches('direction_field', '([^,]+) \d{5}');

answered Apr 19, 2014 at 11:04

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

1 Comment

Indeax Over a year ago

Wolph, thanks, but it didn't work, direction field is more complex and there is postal code repeated and it extract the region code not the city

mraxus · Accepted Answer · 2016-12-20 14:22:10Z

-1

Get a substring from a regex in Redshift:

SELECT REGEXP_SUBSTR(
   'hello_uuid_092bab12-8d8b-40ad-b8b7-bc9f05e52c9c_something_else',
   '([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})'
)

Result: 092bab12-8d8b-40ad-b8b7-bc9f05e52c9c

answered Dec 20, 2016 at 14:22

mraxus

1,4511 gold badge16 silver badges23 bronze badges

Collectives™ on Stack Overflow

Extract string from text field in PostgreSQL (using regex?)

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related