>
> Hello all.
>
> Ive got a table with a field that is varchar(80). This field has a URL
> in it. Unfortunately when the data was entered some of the URL's are
> hardcoded with the IP number. For example:
> http://207.200.200.2/yada/yada.ra
>
> I need to replace the 207.200.200.2 with the domain name. Ive been
> searching and reading for the last day or so, and cannot find an easy
> way to do this without exporting it to an ascii file, then doing a find
> and replace in there, and then re-importing it. Can anyone suggest some
> way that I could modify it using sql? Thanks much,
>
> Darren
>
>
Create and populate another table that can be used to resolve
the IP addresses:
CREATE TABLE ip_to_dom (ip_addr text, dom_name text);
Don't use varchar() for the attributes, because it would fail
later to select the IP address qualified. Then install PL/Tcl
and create the following function:
CREATE FUNCTION url_resolve(text) RETURNS text AS '
if {![regexp "(\\[^:\\]+://)(\\[^/\\]+)(.*)" $1 {} pref ip suff]} {
return $1
}
set n [spi_exec -count 1 \\
"SELECT dom_name FROM ip_to_dom WHERE ip_addr = ''$ip''"]
if {$n == 0} {
return $1
}
return "${pref}${dom_name}${suff}"
' LANGUAGE 'pltcl';
Now you can use something like
UPDATE xxx SET url = url_resolve(url);
to do the job. Since I assume it's a one time job, I did not
create a prepared SPI plan to speed it up. So if you have
thousands of url's in your table and want to do it
periodically, that would be a performance issue.
BTW: I love regular expressions [:-]
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== [EMAIL PROTECTED] (Jan Wieck) #
