CODEWITHSUNDEEP
sqlstringpostgresqlreplace
ngtonhung
ngtonhung

Reputation: 55

Replace characters to right of string in PostgreSQL

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

Upvotes: 1

Views: 214

Answers (1)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

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

Upvotes: 2

Related Questions