Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question

2 Answers

up vote 4 down vote accepted

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:

replace(yourcolumn, 'one.example1.com:9999','example2.com')
share|improve this answer
+1 for pragmatic approach. – Tomalak Jul 30 '12 at 14:05
Thanks , that did the trick. update table SET field = replace(field, 'one.example1.com:9999','example2.com') – ringocub Jul 30 '12 at 14:35
up vote 3 down vote

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'
  )
share|improve this answer
2  
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". – Erwin Brandstetter Jul 30 '12 at 16:18
@Erwin Thanks for the hint. I've included that. – Tomalak Jul 30 '12 at 16:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.