Re: SQL query question

In article <8718@emory.mathcs.emory.edu> RUCHI%CFRVM.bitnet@VTVM2.CC.VT.EDU (Ruchi Patel) writes: > >Is there anyway you could update the lower case data into uppercase by using >the simple SQL UPDATE statement? > >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. > I assume you want to replace 415-926-6622 with 4159266622 rather than with 415 926 6622 since the latter task is too easy (run a series of update statements like this: update tab set phone[2] = " " where phone[2] = "-"; update tab set phone[3] = " " where phone[3] = "-";... update tab set phone[20] = " " where phone[20] = "-") You can do it somewhat similarly with a series of updates like this: update tab set phone[3,19] = phone[4,20], phone[20] = " " where phone[3] = "-"; update tab set phone[4,19] = phone[5,20], phone[20] = " " where phone[4] = "-"; etc i.e. where you find a hyphen in the string, "left shift" whatever follows the hyphen by one character. Obviously, it is a bit of a pain to write these update statements, you will probably want to write a program to generate them. Be careful of the end-cases, check that I haven't got everything off-by-one, and for heaven's sake keep a backup of your data before you use the script to de-hyphenate it! I guess it all depends how badly you need to do it. I have had a whole lot of fun trying to "standardize" a list of 40,000 names and addresses, in order to be able to identify the duplicates. It might be easier to write a 4GL program that will seek-and-destroy hyphens.... Paul