Re: Simple (?) SQL Query.
trevor,
try the following:
create table phones (phone char(17));
insert into phones values ('031231234');
insert into phones values ('091231234');
select *
from phones;
update phones
set (phone)=(phone[1,2]||'9'||phone[3,9])
where phone matches '03*'
;
select *
from phones;
phone
031231234
091231234
phone
0391231234
091231234
>
> 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
>
--
regards,
+----------------------------------------------------------------------------+
| . . | Bob Baskett |
| ... ... | Software Engineer |
| ..... ..... | Business Systems Integration Group |
| .. ... .. | Semiconductor Products Sector |
| . . . | Mesa, AZ |
| | President, Informix Users Group Of Arizona |
| Motorola, Inc. | |
+----------------------------------------------------------------------------+
| Sun 690MP 4.1.3, Online 5.01, 4.10.UD1 Tools, Fourgen v4.10.UC1 |
+----------------------------------------------------------------------------+