Regular expression find and replace in Postgres

Question

I have a table that contains a number of rows with columns containing a URL. The URL is of the form:

http://one.example1.com:9999/dotFile.com

I would like to replace all matches in that column with http://example2.com/dotFile.com while retaining everything after :9999. I have found some documentation on regexp_matches and regexp_replace, but I can't quite wrap my head around it.

Tomalak · Accepted Answer · 2012-07-30 16:25:10Z

79

To replace a fixed string, use the simple replace() function.

To replace a dynamic string, you can use regexp_replace() like this:

UPDATE
  YourTable
SET
  TheColumn = regexp_replace(
    TheColumn, 'http://[^:\s]+:9999(\S+)', 'http://example2.com\1', 'g'
  )

edited Jul 30, 2012 at 16:25

answered Jul 30, 2012 at 14:04

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

4 Comments

Erwin Brandstetter Over a year ago

replace() does a simpler job here, as already you commented yourself. However, to replace "all matches" with regexp_replace(), you have to add the 4th parameter 'g' .. for "globally".

Tomalak Over a year ago

@Erwin Thanks for the hint. I've included that.

ArchNoob Over a year ago

I was looking for one I can use in a where clause like ` UPDATE ... WHERE "email" = regexp_matches("email", E'.[co.tz]')` so to filter and improve the update speed. But this is alright too, since I'm only doing this once, in developement. Thank you, for I really wanted a regexp solution tho. :)

Tomalak Over a year ago

If no part of your search string is variable, using regex is pointless. Since regex are much slower than basic string searches I would try to avoid them whereever possible. A "last 5 characters of the column = 'co.tz'" type of search will be faster than the regex equivalent.

laconbass · Accepted Answer · 2021-11-22 16:06:10Z

38

if you know the url, you don't have to use regex. replace() function should work for you:

replace(string text, from text, to text)        
Replace all occurrences in string of substring from with substring to   
example: replace('abcdefabcdef', 'cd', 'XX')    abXXefabXXef

you could try:

UPDATE yourtable SET
  yourcolumn = replace(yourcolumn, 'one.example1.com:9999','example2.com')
;

edited Nov 22, 2021 at 16:06

laconbass

18k9 gold badges54 silver badges57 bronze badges

answered Jul 30, 2012 at 14:04

Kent

197k36 gold badges248 silver badges317 bronze badges

2 Comments

ringocub Over a year ago

Thanks , that did the trick. update table SET field = replace(field, 'one.example1.com:9999','example2.com')

Some random IT boy Over a year ago

This comment is what the answer is missing

Collectives™ on Stack Overflow

Regular expression find and replace in Postgres

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related