Miguel Fernandez Miguel Fernandez - 5 months ago 42
MySQL Question

How to display MySQL value with zerofill

Im using MySQL, ajax and c# for my website, I have a page with an input which display this query:

Select MAX(Id) from table


Then in my ajax call in the success function I have this:

var id = data.d + 1;


At the moment in my empty table I have Id with identity and autoincrement = 1, so var id = 0 + 1; so my input value its 1 and then changes when a row its inserted.

Problem:

I want to start identity in 00001, I alter table and add zerofill, so now my first row its 000001, but when I want to display the next id in my input shows 0, 1, 2 or whatever but without 0's.

Question:

How can I display the value with 0's?

Answer

You can use lpad (left pad) to show a numeric value as zero-fill text:

Select lpad(MAX(Id),5,'0') from table;

Edit: A zero-filled value in Javascript will be converted to numeric and the result will be a regular numeric (non zero-fill)

Javascript:

var id = ("00000" + (parseInt(data.d) + 1));
var zerofillid = id.substring(id.length -5); // =00002

You can use zerofillid to display in your end.

Comments