Okay, so if it won't work with more than one space, are thos records "doomed" to upper only or First letter capitalized?
-----Original Message----- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 11:10 AM To: 'Gill, Vern' Cc: '[EMAIL PROTECTED]' Subject: RE: Changing data Try the following 2 queries. UPDATE table SET name=concat(ucase(left(name,1)), lcase(SUBSTRING(name,2))); UPDATE table SET name=concat(left(name, INSTR(name, ' ')), ucase(mid(name,INSTR(name, ' ')+1,1)), substring(name FROM INSTR(name, ' ')+2)) WHERE INSTR(name, ' ') > 0; The first query will capitalize the first letter of every entry and make the rest lower case. The second query will find the first blank, then capitalize the proceeding letter. Note that for names that have more than one space in them ("PRO SPORTS TEAM"), this won't work. But you can find those records that have more than one space in them by running THIS query: SELECT * FROM table WHERE name REGEXP ".* .* .*" - Jonathan -----Original Message----- From: Gill, Vern [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 10:45 AM To: 'Jonathan Hilgeman' Subject: RE: Changing data Cool. Thank everyone for help with that. Now, one more question; Can I use MySQL to change the data's case? I.E.; PROS TEAM -------------> Pros Team PINEAPPLES -------------> Pineapples RAPID PRINTING -------------> Rapid Printing -----Original Message----- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 8:27 AM To: 'Gill, Vern'; '[EMAIL PROTECTED]' Subject: RE: Changing data Try this query: UPDATE table SET phone = concat("(",left(phone,3),") ",mid(phone,3,3),"-",mid(phone,6,4)); It should convert 0000000000 to (000) 000-0000. Take a look at this page if you want details on how it works: http://www.mysql.com/doc/S/t/String_functions.html - Jonathan -----Original Message----- From: Gill, Vern [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 3:23 AM To: '[EMAIL PROTECTED]' Subject: Changing data how would I change in all rows data that is; 0000000000 to 000-000-0000 or (000) 000-0000 Can this even be done with mysql? Thank you in advance... Vern H. Gill State Director Director of Marketing Conejo Valley Jaycees http://www.conejovalleyjaycees.org --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
