Hello,

I'm wondering if there is a way in SQL to find and replace a specific string
within various database records, the way you might across a text document.
Not to replace the entire value of the fields in which the string is found,
but just that specific string.

The context of this is that I was a developing a site on a test server, and
then moved it to the domain where the live site is going to be hosted, only
to realize that (primarily due to bad planning on my part), there are
references to the test domain sprinkled through a particular text field
(mostly as part of img tags, since the contents of that field are HTML).

I know the optimal solution would be not to have done that in the first
place :-) , but hindsight is 20/20 and now I need to find a way to fix it.
So is there a way to search out all occurrences of, say, the string
"testsite.mydomain.com" within a specific column and change it to
"www.theirdomain.com" wherever it's found?


Lynna

--
Spider Silk Design - http://www.spidersilk.net
509 St Clair W Box 73576, Toronto ON Canada M6C 1C0
Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289

Search Discussions

  • Andrew Chambers at Jan 18, 2006 at 12:48 am

    On 17:09 Tue 17 Jan , Lynna Landstreet wrote:
    Hello,

    I'm wondering if there is a way in SQL to find and replace a specific string
    within various database records, the way you might across a text document.
    Not to replace the entire value of the fields in which the string is found,
    but just that specific string.
    http://www.postgresql.org/docs/8.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

    [snip]
    So is there a way to search out all occurrences of, say,
    the string "testsite.mydomain.com" within a specific column and change
    it to "www.theirdomain.com" wherever it's found?
    regexp_replace(field, 'testsite\.mydomain\.com', 'www\.theirdomain\.com' [,flags])

    Regards,
    Andy

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 17, '06 at 10:10p
activeJan 18, '06 at 12:48a
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase