Re: SQL query question

In article <1992Apr14.153529.27934@nas.nasa.gov> jcarter@sun419.nas.nasa.gov (John R. Carter Sr.) writes: >In an article regarding SQL query question Ruchi Patel writes: >> Is there anyway you could update the lower case data into uppercase... >> Is there anyway you could delete a character from the column... > >Although I like the sophisticated method submitted by Paul Roberts, >I prefer to perform an ASCII dump of the offending records and let UNIX do >the conversion, then reload the records. You may find this to be a bit faster >and far less of a problem. UNIX can 'sed', 'tr', 'awk' or even 'perl' >your most gruesome task with ease. It also has the advantage of taking >such mundane tasks out of the database leaving it to do what it does best. >The disadvantage is that you need to lock the tables from updates while >the data is 'outside' being modified, or do this at a time when you know >no one is using the database. If you must have more real time access then >Paul's solution is best. > I must concur with Mr. Carter's response on this. If the data you are converting has any phone numbers that are not in the standard US or Canadian format, eg any European or other international phone numbers, the basic updates shown earlier will miss them. If you are in a position where unloading the phone numbers to an ascii file is possible, then using a sed, awk, vi, or whatever to search and replace all the hyphens, then reloading the data (removing the orginals first) is the most complete method. (All this assuming you don't have 4gl or ESQL to set up a cursor and scan through the offending records, updating them where needed) -- /|| // // ,'/""\\' John Postma john@mks.com (519)884-2251 / ||/// //\\' `\\\\\\ Database Administrator, Mortice Kern Systems Inc. / |/ /_// \\\\___/ 35 King St. N., Waterloo, Ont., Can. N2J 2W9 o/ "Things are only impossible until they're not!" -- Capt Jean Luc Picard