PostgreSQL replace characters to right of string

Question

I need to clean up an address field in PostgreSQL 8.4 by removing everything to the right of a street name. This includes dropping suites ("100 Broadway Street Suite 100") and correcting names that have unit numbers appended to the street name ("100 Broadway Street100") so that the result in both cases would be "100 Broadway Street".

Essentially I am trying to remove everything to the right of "Street". I can't seem to get a replace function to work without individually coding for each case. A rtrim function also doesn't work because the characters I want removed would be a wildcard.

Here is what I am trying to get to work:

update *tablename* set *fieldname* = replace (*fieldname*, '%STREET%', '%STREET')

This SQL below works, but I don't want to code each possible combination:

UPDATE *tablename* set *fieldname* = replace (*fieldname*, ' SUITE 100', '');
UPDATE *tablename* set *fieldname* = replace (*fieldname*, ' STREET100', ' STREET');

How can I remove everything to the right of a string "Street" without explicitly specifying what follows "Street"?

Thanks for the help.

Josh Kupershmidt · Accepted Answer · 2014-12-15 18:35:47Z

1

Try something like this:

SELECT regexp_replace('100 broadway street 100', '(.*)(Street).*', '\1\2', 'i');

The above is basically looking for anything followed by "Street" (case insensitively, per the last 'i' agrument), and then stripping off everything after the "Street", which I think is what you're asking for. See http://www.postgresql.org/docs/current/static/functions-matching.html for more details.

answered Dec 15, 2014 at 18:35

Josh Kupershmidt

2,7561 gold badge26 silver badges33 bronze badges

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

Comments

Glenn · Accepted Answer · 2014-12-15 18:55:29Z

0

This truncates after the first instance of 'STREET':

UPDATE tablename
  SET fieldname = SUBSTR(fieldname, 1, position('STREET' IN fieldname) + 5)
  WHERE fieldname LIKE '%STREET%'

Update: If the desire is to have a case-insensitive search of "STREET":

UPDATE tablename
  SET fieldname = SUBSTR(fieldname, 1, position('STREET' IN UPPER(fieldname)) + 5)
  WHERE UPPER(fieldname) LIKE '%STREET%'

edited Dec 15, 2014 at 18:55

answered Dec 15, 2014 at 18:40

Glenn

9,2102 gold badges44 silver badges56 bronze badges

2 Comments

Josh Kupershmidt Over a year ago

Note, although OP's examples use "STREET" (all-caps), he said in his original description 'I am trying to remove everything to the right of "Street"', so I'm assuming he really wants a case-insensitive search for "Street".

jtodd Over a year ago

Thanks for the reply. I already updated the field to be all upper case, but both snippets are useful.

Collectives™ on Stack Overflow

PostgreSQL replace characters to right of string

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related