Re: Simple (?) SQL Query.
> From: Trevor K Staats <100355.122@CompuServe.COM>
> Subject: Simple (?) SQL Query.
> Date: 3 May 1995 02:09:18 GMT
> Reply-To: Trevor K Staats <100355.122@CompuServe.COM>
> Organization: Australis Microcomputer
>
> 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,
I don't know if the newer versions of Informix support an SQL function
like the substr() function provided by Oracle. My older and out of
support version does not have this.
On the off-chance that this is available, here is the Oracle syntax,
to be modified as needed for the Informix equivalent:
update site
set phone = substr(phone,1,2) || '9' || substr(phone,3)
where phone like '03%'
;
If substr() is not available, then you need to:
1. Write a 4GL or ESQL/C program to make the change, or
2. Run the following query, with output to a file, then make the changes
that I note below.
select "update site set phone =""", phone, """ where phone = """, phone, """;"
from site
where phone like "03%"
;
You might need to use slightly different syntax to get your output to include
the necessary quote marks. Once you have edited off any header and trailer
lines from the output, use your editor's "replace first occurrence in line"
command to change the 03 in the first phone output to 039. For example, in
vi the command would be
1,$s/03/039/
Be certain to NOT put a "g" at the end of the command, so that only the first
occurrence of 03 is changed in each line. You may also need to edit a bit to
eliminate unwanted spaces around your phone numbers. Now you have a long SQL
script that you can run to update your database.
Hope this helps,
Regards,
Alan
+---------------------------+-----------------------------------------------+
| R. Alan Popiel | Internet: alan@den.mmc.com |
| Lockheed Martin, SLS | Voice: 303-977-9998 |
| P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. |
| Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. |
+---------------------------+-----------------------------------------------+