Re: Simple (?) SQL Query.
Trevor K Staats <100355.122@CompuServe.COM> wrote:
>
> In Melbourne our telephone numbers will soon change from
> (03)xxx-xxxx to (039)xxx-xxxx format. We have thousands of these
> numbers stored in an informix database as part of a fault tracking
> system, and we need to change them. I have tried a few SQL
> queries, but I cannot seem to get the "SET" clause to insert the 9
> into the existing number. I am using:
> update site set phone=????
> where site.phone like "03%";> The ???? is where I am still stumped.
> Any ideas?
>
Well, you could use a unload/load-sequence, correcting data
with 'normal' UNIX-tools (vi?) between.
As you are able to reproduce the data any time using this
scenario, you could turn off logging for your database,
saving lots of time.
However, you probably could also use a SQL simelar to
the following:
UPDATE site SET phone='(039)'||phone[5,len] -- len = length(phone)
WHERE phone LIKE "(03)%";
Hope this helps,
Peter