Re: SQL query question

Dave Snyder writes (quoting Ruchi Patel) : > -> Is there anyway you could update the lower case data into uppercase by using > -> the simple SQL UPDATE statement? > No. > > -> Is there anyway you could delete a character from the column using the > -> UPDATE statement? e.g. I would like to drop a "-" character from the > -> phone_no column. > No. Ok, so how about cheating ? :-) For the first one: ---8<-------8<-------8<-------8<-------8<-------8<-------8<-------8<---- echo 'unload to "fred.unl" select * from fred;' | isql mydb echo '%s/^[^|]*/\\L&/|wq' | ex fred.unl echo 'load from "fred.unl" insert into fred;' | isql mydb ---8<-------8<-------8<-------8<-------8<-------8<-------8<-------8<---- Backslash L drops matched chars (&) to lower-case, %s does it on all lines. This is only downshifting the first field (easiest), so arrange the fields in the SELECT to suit this. A pipe separates ex commands. You can use a similar dirty hack to get rid of the hyphens; or try %s/-/ /g if you haven't got any apart from in the 'phones ... Otherwise, it's easy (but *still* harder) in 4GL and using an UPDATE. Cheers - Tony. PS. So when's Informix going to come out with one of these *advanced* hierarchical databases I keep hearing about, then ? %-) __________________________________________________________________________ Tony Heskett th@bnr.co.uk Voice: (+44) 279 429531 x 2637 BNR, London Road, Harlow, Essex, CM17 9NA Fax: (+44) 279 454187