Thread: Finding and replacing text within records

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

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