Tired of Oracle answers to an Informix questions.

Alan: Informix has supported sub-strings for as long as I have been coding with ISQL and 4GL which has been sense 1985. Yes ; You of course had to read the manual to find out that all you had to do was treat a character string as you would any character string or array in C. Do you think that you can not sub-string in C either ? If everyone wanted to do things the way Oracle does them, no one (not Oracle, Sybace, or Informix) would improve as no one would be looking for better ways. Just because something or someone does not do things your way, does not mean they do not do them. Oracle solutions are great for Oracle users. Should I join the Oracle forum to get Informix information ? I hope not! Chuck Francis ---------- > Items deleted --------- 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. | +---------------------------+-----------------------------------------------+