display numbers with zero pad I have some data stored as an unsigned tinyint.  When displaying it I want it to be zero padded only if it is 1 digit in length.  I have issues with LPAD(CAST(NUMBER AS CHAR CHARSET UTF8),2,'0') when I have three digit long numbers.  How do I get what I'm looking for?

Example of data in table:

NUMBER
1
2
3
...
10
11
...
101
102


What I currently get when running with LPAD

LPAD_NUMBER
01
02
03
...
10
11
...
10
10


What I want

UNKNOWN
01
02
03
...
10
11
...
101
102


SOLUTION I USED

select case 
    when number between 0  and 9 then
        LPAD(CAST(number AS CHAR CHARSET UTF8), 2, '0')
    else
        CAST(number AS CHAR CHARSET UTF8)
    end as newNum;


This gets me what I'm looking for and is based off of the accepted answer.
You can use a case expression:

select case when number between 0 and 9 then '0' else '' end 
    || number as n 
from t;


Note that || is the sql-standard operator for string concatenation. Unless properly configured MySQL will treat || as logical OR. This behavior is determined by the setting PIPES_AS_CONCAT. You can also enable this and other sane settings with ANSI. You can see what settings you have with:

select @@sql_mode;


It can be changed using: 

set @@sql_mode=@@sql_mode || ',PIPES_AS_CONCAT';


or by modifying the config file

However this is purely presentation so I think you should do the formatting of the result in the application instead.