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.