Hi, I am puzzled not to see a funciton like initcap of oracle in mysql. Perhaps it is already there.
Here is on (ugly) work around : Update table set name = replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace (replace(replace(replace(replace(replace(replace(concat(ucase(left(name,1)), lcase(substring(name,2))),' a',' A'),' b',' B'),' c', ' C'),' d', ' D'), ' e', ' E'),' f', ' F'),' g', ' G'),' h', ' H'),' i', ' I'),' j', ' J'),' k', ' K'), ' l', ' L'),' m', ' M'),' n', ' N'),' o', ' O'),' p', ' P'),' q', ' Q'),' r', ' R'), ' s', ' S'),' t', ' T'),' u', ' U'),' v', ' V'),' w', ' W'),' x', ' X'),' y', ' Y'), ' z', ' Z'); This will handle any number of words in name as well as any number of successive spaces. I am sorry if it seems too ugly. Anvar. At 08:03 PM 03/12/2001 -0800, you wrote: >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 --------------------------------------------------------------------- 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
