Replace characters to right of string in PostgreSQL

Question

I am in need of solving a problem for my project.

I need to clean up an address field in PostgreSQL by removing everything to the right of a street name.

And I have found it here: PostgreSQL replace characters to right of string

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

However, I would like to replace '100 broadway street 100' more flexibly, like this:

SELECT regexp_replace('100 broadway street 100', '(.*)(Street OR Str. OR Ward OR W. OR Dist).*', '\1\2', 'i');

Can someone help me write the right syntax or have any other links I haven't found yet?

Input 1: "100 Alexandre de Rhodes Street, District 10, HCM City"

Input 2: "100 Quang Trung Str., District 10, HCM City"

Input 3: "123 Newton St., GV District, HCM City" Output 1: "100 ABC Street, Ward 16" Output 2: "100 Quang Trung Str." .v.v..

ie will need to remove the string behind the road name

Show us some more sample data and the expected result. All as formatted text, i.e. no images. — jarlh
– jarlh, Commented Oct 31, 2019 at 9:25
Input 1: "100 Alexandre de Rhodes Street, District 10, HCM City" Input 2: "100 Quang Trung Str., District 10, HCM City" Input 3: "123 Newton St., GV District, HCM City" Output 1: "100 ABC Street, Ward 16" Output 2: "100 Quang Trung Str." ....v...v.... ie will need to remove the string behind the road name — ngtonhung
– ngtonhung, Commented Oct 31, 2019 at 9:47
I will note this for later. I have additional updates at the original question. — ngtonhung
– ngtonhung, Commented Oct 31, 2019 at 10:07

Arun Palanisamy · Accepted Answer · 2019-10-31 11:15:48Z

2

I think you are looking for | operator like this

SELECT regexp_replace('100 broadway Dist 100', '(.*)(Street|Str|Ward|Dist).*', '\1\2', 'i');

Output

100 broadway Dist

Update based on comments

You can replace .* with ..

SELECT regexp_replace('100 broadway Dist Str 100 Str abc Street', 
                      '(.)(Street|Dist|Ward|Str).*', '\1\2', 'i');

Output

100 broadway Dist

edited Oct 31, 2019 at 11:15

answered Oct 31, 2019 at 9:33

Arun Palanisamy

5,4898 gold badges33 silver badges57 bronze badges

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

5 Comments

ngtonhung Over a year ago

It seems like it doesn't work when I replace the string with "100 broadway Dist 100 Str abc", the result will produce "100 broadway Dist 100 Str" instead of my desired "100 broadway Dist"

Arun Palanisamy Over a year ago

@ngtonhung , You mean to say the same string may contain both Dist and Str? If so what is the order of precedence ?

ngtonhung Over a year ago

The order of precedence will be left to right (words that appear first will remove all words to their right)

ngtonhung Over a year ago

Thank you very much. Your answer solved my problem.

Arun Palanisamy Over a year ago

@ngtonhung, Welcome! Glad i could help.

Collectives™ on Stack Overflow

Replace characters to right of string in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related