CODEWITHSUNDEEP
regexpostgresql
ringocub
ringocub

Reputation: 457

Regular expression find and replace in Postgres

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.

Upvotes: 44

Views: 64029

Answers (2)

Kent
Kent

Reputation: 195049

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')
;

Upvotes: 38

Tomalak
Tomalak

Reputation: 338208

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

Upvotes: 79

Related Questions