RE: Changing data

[Jonathan Hilgeman <JHilgeman@xxxxxxx>]

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:vgill@xxxxxxxxxxxxxxxx]
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:JHilgeman@xxxxxxx]
Sent: Monday, December 03, 2001 8:27 AM
To: 'Gill, Vern'; 'mysql@xxxxxxxxxxxxxxx'
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:vgill@xxxxxxxxxxxxxxxx]
Sent: Monday, December 03, 2001 3:23 AM
To: 'mysql@xxxxxxxxxxxxxxx'
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 <mysql-thread92886@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-JHilgeman=ecx.com@xxxxxxxxxxxxxxx>
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 <mysql-thread92919@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php