Zero Fill a number in ColdFusion

I need to have a 10 character field on a SQL server table that contains all numberics as an account number.  I want it as character so I can display it as zero filled to the left.  EG, "000000018" .

Q:  Using Coldfusion, how do I create the next sequential account id?  If I retieve the column from the table, what do I get with :
<cfset newid = tableid + 1>

If I make the field a numneric field on the table (as an Identity), how do I display it with leading zeros?  Is their a function for this?

Thanks in advance,
Who is Participating?
PluckaConnect With a Mentor Commented:
numberFormat(123, '0000000000')

This will actually result in a string, though CF does the type conversion, you will need to store it in the database as a varchar or similar.

Aneesh RetnakaranDatabase AdministratorCommented:
you can  display like this

  LEFT ( replicate('0', 10) + cast (  IDENT_CURRENT('table_name')  as varchar ), 10 )
hefterrAuthor Commented:
Hi aneeshattingal,
I think I found the function NumberFormat:

Here is a test sample:

<cfset testnum = "000000010">
<cfset newnum = testnum + 1>
<cfset charnumber = numberformat(newnum,'0000000000')>

The original value is #testnum# <br />
The new value is #newnum#<br />
This is the char number : #charnumber#
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

> <cfset testnum = "000000010">
> <cfset newnum = testnum + 1>

That's just an example, right? You're letting the database handling creating unique numbers. Then padding the display on the front end? Because the do-it-yourself method is almost always a mistake ;-)
hefterrAuthor Commented:
Hi aqx,
Yes and no!  I am getting the numeric column from the table.  I then expand it to 10 characters and prefix it with and account type (eg "G0000000010") gor a government account.  This then becomes a separate column and key to the table.

It's a little clugdy, (I admit), but I'd like to have the character key for sorting and easy display.

So the base number is from some sort of identity column?  That sounds safer as long as the identity column value cannot be larger than 10 digits.  Whatever option you choose, if the "key" must be unique,  consider adding a unique constraint. Just to prevent bad things from happening if a duplicate does occur for whatever reason.

That said,  numeric keys are generally better candidates for for searching and indexing than strings. They're smaller and comparisons are faster.  Plus you can always format values on the front end, or inside a sql "view", without changing the internal representation.

hefterrAuthor Commented:
Thanks for the advice.  I'm not sure if I can do with the alphanumneric column or not.  An Alias is a good idea for dealing with that issue.  Ihaven't worked with htem in MS SQL Server as I am formally a DB2 guy so I do understand the concept.

You're welcome. Yes, I tend to prefer the view/alias approach. We do that a lot in our apps.  Some variation of what aneeshattingal posted would do the trick.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.