Add zero to the values to force a string-to-number conversion : Char « Data Type « SQL / MySQL

1. SQL / MySQL
2. Data Type
3. Char

Add zero to the values to force a string-to-number conversion

      
mysql>
mysql> CREATE TABLE roster
    -> (
    ->     name CHAR(30), # player name
    ->     jersey_num CHAR(3) # jersey number
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO roster (name, jersey_num)
    ->  VALUES
    ->          ('Lynne','29'),
    ->          ('Ella','0'),
    ->          ('Elizabeth','100'),
    ->          ('Nancy','00'),
    ->          ('Jean','8'),
    ->          ('Sherry','47');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT name, jersey_num FROM roster;
+-----------+------------+
| name      | jersey_num |
+-----------+------------+
| Lynne     | 29         |
| Ella      | 0          |
| Elizabeth | 100        |
| Nancy     | 00         |
| Jean      | 8          |
| Sherry    | 47         |
+-----------+------------+
6 rows in set (0.00 sec)

mysql>
mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num+0;
+-----------+------------+
| name      | jersey_num |
+-----------+------------+
| Ella      | 0          |
| Nancy     | 00         |
| Jean      | 8          |
| Lynne     | 29         |
| Sherry    | 47         |
| Elizabeth | 100        |
+-----------+------------+
6 rows in set (0.00 sec)

mysql>
mysql> drop table roster;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
  

Related examples in the same category