CODEWITHSUNDEEP
stringpostgresqlreplacewildcard
jtodd
jtodd

Reputation: 1

PostgreSQL replace characters to right of string

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.

Upvotes: 0

Views: 2584

Answers (2)

Glenn
Glenn

Reputation: 9150

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%'

Upvotes: 0

Josh Kupershmidt
Josh Kupershmidt

Reputation: 2710

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.

Upvotes: 1

Related Questions