Re: Convert varchar to number field
Steve Werby wrote: Martin E. Koss [EMAIL PROTECTED] wrote: I started a table with a varchar filed of 11 characters for a column that would hold data such as 0.27; 11; 0.6; 1; 5; 7.5; 11.2; etc I wasn't sure if a 'number' field would work. Now I want to list the values in this column and they do not list numerically, for example: if we had 0.27; 0.6; 1; 5; 7.5; 11; then 11 would come before 5 and 7.5 (alphanumerically). What I need is them to list numerically, so they would list like: 0.27 0.6 1 5 7.5 11 11.2 Any advice on what to change the field type to or how to convert it at the time of querying via PHP would be great. Thanks. Use an ALTER TABLE statement. Something like the following: ALTER TABLE table_name MODIFY field_name DECIMAL(4,1) UNSIGNED ALTER TABLE table_name MODIFY old_field_name new_field_name DECIMAL(4,1) UNSIGNED Old_field_name and new_field_name can be the same. Make sure you choose a numeric type that is appropriate. See the online manual for options. If you're worried you might ruin your data with this conversion first create a copy of the table and experiment on it instead. CREATE TABLE my_copy SELECT * FROM original_table Note that it won't copy the indexes, but it will create a copy of the table. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.com/ - 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
Convert varchar to number field
Hi, I started a table with a varchar filed of 11 characters for a column that would hold data such as 0.27; 11; 0.6; 1; 5; 7.5; 11.2; etc I wasn't sure if a 'number' field would work. Now I want to list the values in this column and they do not list numerically, for example: if we had 0.27; 0.6; 1; 5; 7.5; 11; then 11 would come before 5 and 7.5 (alphanumerically). What I need is them to list numerically, so they would list like: 0.27 0.6 1 5 7.5 11 11.2 Any advice on what to change the field type to or how to convert it at the time of querying via PHP would be great. Thanks. Martin - 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
Re: Convert varchar to number field
"Martin E. Koss" [EMAIL PROTECTED] wrote: I started a table with a varchar filed of 11 characters for a column that would hold data such as 0.27; 11; 0.6; 1; 5; 7.5; 11.2; etc I wasn't sure if a 'number' field would work. Now I want to list the values in this column and they do not list numerically, for example: if we had 0.27; 0.6; 1; 5; 7.5; 11; then 11 would come before 5 and 7.5 (alphanumerically). What I need is them to list numerically, so they would list like: 0.27 0.6 1 5 7.5 11 11.2 Any advice on what to change the field type to or how to convert it at the time of querying via PHP would be great. Thanks. Use an ALTER TABLE statement. Something like the following: ALTER TABLE table_name MODIFY field_name DECIMAL(4,1) UNSIGNED Make sure you choose a numeric type that is appropriate. See the online manual for options. If you're worried you might ruin your data with this conversion first create a copy of the table and experiment on it instead. CREATE TABLE my_copy SELECT * FROM original_table Note that it won't copy the indexes, but it will create a copy of the table. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.com/ - 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