Re: Simple (?) SQL Query.
On 3 May 1995, Trevor K Staats 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?
>
> Thanks,
> Trevor Staats
> CS 100355,123
>
> --
> Trevor Staats - Australis Microcomputer
> 1 Keith Campbell Court
> Scoresby VIC 3179 AUSTRALIA
>
Trevor,
This is quick and dirty, so please pardon my French.
update site.phone
set phone=phone[1,2]||9||phone[3,10] ;
In Isql, you'll have to answer the prompt: "Update all" with a Y.
Note, if you're storing the parens and dashes, the character positions
will have to be adjusted.
Yours,
Nick
*********************************
Nick Nobbe, Library of Congress
NLS/BPH
mail: nnob@loc.gov
*********************************