?
Solved

Zero Fill a number in ColdFusion

Posted on 2009-12-21
8
Medium Priority
?
754 Views
Last Modified: 2013-12-16
Hi,
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>

OR
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,
hefterr
0
Comment
Question by:hefterr
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26100895
you can  display like this

  LEFT ( replicate('0', 10) + cast (  IDENT_CURRENT('table_name')  as varchar ), 10 )
0
 
LVL 1

Author Comment

by:hefterr
ID: 26101059
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')>

[code]
<cfoutput>
The original value is #testnum# <br />
The new value is #newnum#<br />
This is the char number : #charnumber#
</cfoutput>
[/code]
0
 
LVL 18

Accepted Solution

by:
Plucka earned 2000 total points
ID: 26101290
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.

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 52

Expert Comment

by:_agx_
ID: 26106755
> <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 ;-)
0
 
LVL 1

Author Comment

by:hefterr
ID: 26107138
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.

Thanks,
hefterr
0
 
LVL 52

Expert Comment

by:_agx_
ID: 26108429
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.

0
 
LVL 1

Author Comment

by:hefterr
ID: 26108731
aqx,
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.

hefterr
0
 
LVL 52

Expert Comment

by:_agx_
ID: 26108911
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question