Re: Simple (?) SQL Query.

> > 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? If phone were broken down to 3 fields this kind of change is simple. That is why I've long been an advocate of area_code CHAR(3) pref_code CHAR(3) phone CHAR(4) This does not seem to be the case for you. You could change it to this by changing your schema to above and working with substrings ie. Update site set area_code = phone[2,3] UPDATE site set pref_code = phone[5,7] UPDATE site set phone_no = phone[9,12] UPDATE site set area_code = "039" WHERE area_code = "03" ALTER table site DROP phone This requires changing your application. Another alternative that would not require changing your application (unless the application uses substrings of the phone attribute would be to use 4GL or some other language ie. SELECT phone FROM site WHERE ... LET new_phone = phone[1,2] IF new_phone = "03" THEN LET new_phone = "039" LET new_phone = new_phone, phone[5,12] UPDATE site SET phone = newphone WHERE ... You would use a FOREACH or similar loop to do this The syntax here is not gauranteed but you should get the idea. Hope this helps Michael > > Thanks, > Trevor Staats > CS 100355,123 > > -- > Trevor Staats - Australis Microcomputer > 1 Keith Campbell Court > Scoresby VIC 3179 AUSTRALIA >