Pad ID column with leading zeros (was "How to do this in mysql?")

in MS SQL 2000
I have a testing table in this table :
[ID] [int] IDENTITY (1, 1) NOT NULL
and i think all tables have this column :slight_smile:
so table Testing like this

ID

1
2
3
4
5
…
10
11
…
100
101

so i want a sql query for retrieving Item Code which is the ID in the comming format :

ItemCode

00001
00002
00003
00004
00005
…
00010
00011
…
00100
00101
…

and i make a query:
SELECT Replicate(β€˜0’,5-DataLength(convert(varchar,ID)))+β€˜β€™+Convert(varchar,ID) As ItemCode FROM Testing

this query is working fine but i think that if the value of DataLength(convert(varchar,ID)) is more than 5 then the ItemCode is Null

And the major problem is how to convert this sql query into mysql query?

let me ask you – if the ID value is larger than 99999, what do you want to show?

in mysql, you may use lpad(ID,5,β€˜0’) for the whole thing

First thank you for your fast reply

i don’t know what iam going to do so iam asking
and could you write this sql query in MS SQL 2000 and mysql please

for sql server, use what you have already, or this:

SELECT right('00000'+cast(ID as varchar),5) As ItemCode FROM Testing

for mysql, use this:

SELECT lpad(ID,5,'0') As ItemCode FROM Testing

thank you !