Adding Leading Zeros to my column

[vgrapher]

I've seen other posts with mySQL syntax for adding leading zeros to my column but it doesn't seem to be working. Here's what I need.

Table Name: MyCustomers
Column Name: CustomerID

The data in this column goes something like:
1
2
3
4
5
etc.

I would like it to look like:
0001
0002
0003
0004
etc.

Can someone point out the correct MySQL syntax to make it work?

Thanks...

 

[gmmastros]

This forum is specifically for SQL Server, so this may not work with MySQL.

With SQL Server, you first need to convert the int column to a string (varchar). Then you add a series of 0's to the front, and then take the string from the right.

Ex:
[tt]
Num Num with 0's Right 4 characters
--- ------------ ------------------
1 00001 0001
2 00002 0002
10 000010 0010
20 000020 0020
100 0000100 0100
200 0000200 0200
[/tt]

Select CustomerID, 
       Right('0000' + Convert(VarChar(10), CustomerId), 4)
From   MyCustomers

Like I said, the syntax for MySQL may be slightly different, but the concept works. Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vgrapher]

George, thanks so much for responding. I understand the code concept-wise. However, when I use it, I still get a syntax error.

I was hoping that someone can provide the exact MySQL syntax to use.

Thanks again,
Cyril

 

[gmmastros]

In that case, I encourage you to find a MySQL specific forum to post your question.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Check LPAD() function

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lpad

PluralSight Learning Library

 

[wvdba]

try this:
Select CustomerID,
Right(concat('0000', CustomerId), 4)
From MyCustomers

 

[vgrapher]

That is the perfect syntax for MySQL. Thanks so much!!!!