Adding leading 0's to a returned value from SQL

[harrypsk]

I have numbers stored in a sql table. My .asp page will return those numbers, but I need them to be formatted like this:

00001
00010
00100
01000
10000

So, I need the number to always be 5 digits in length. The database will return a digit, either single or up to 4 digits, but I alway need enough leading zeros added to the digit in order to fit the 5 digit requirement. I'm using .asp but I'm not that skilled in specific programming of vbscript or javascript in order to get the result I need. Thanks for the input. Let me know if you need more information.

[CaseyRyan]

In ASP when outputing that value, you can do this:

Dim myPaddedNumber, myOutputNumber 
' 
'/ Get the value from your recordset and add 5 leading zeros /' 
' 
myPaddedNumber = "00000" & rs("Number") 
' 
'/ Now Take the Right 5 Characters From that String 
' 
myOutputNumber = Right(myPaddedNumber,5) 
' 
'/ Write it out 
' 
Response.Write(myOutputNumber) 

You could easily make this into a function too. It would look like this:

Function PadMyNumber(value) 
padMyNumber = Right("00000" & value,5) 
End Function 

-=casey=-

[harrypsk]

Excellent, that works, but I need to repeat the returned records, here's what I have so far:

The code you provided, grabbing the correct field to add 0's too:

Dim myPaddedNumber, myOutputNumber
'
'/ Get the value from your recordset and add 5 leading zeros /'
'
myPaddedNumber = "00000" & rs_vnumber.Fields.Item("vnumber").Value
'
'/ Now Take the Right 5 Characters From that String
'
myOutputNumber = Right(myPaddedNumber,5) <%

Here's where I display the record set and call the result of your code:
While ((Repeat1__numRows <> 0) AND (NOT rs_vnumber.EOF))
%>
<p><%=Response.Write(myOutputNumber)%>
<%=(rs_vnumber.Fields.Item("vendorname").Value)%>
<%=(rs_vnumber.Fields.Item("username").Value)%></p>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rs_vnumber.MoveNext()
Wend
%>

So, as you can see I use Dreamweaver, I'm not a great coder. How do I make each part of the recored add the 0's to the number? I undstand that the first records number is showing for all records, as myOuputNumber only has one value, so it's repeated over and over. Is there clear enough? Thanks for the help thus far!

[CaseyRyan]

Here's what you should use. I just incorporated the function into it.

<% 
Function PadMyNumber(value)  
padMyNumber = Right("00000" & value,5)  
End Function  
' 
While ((Repeat1__numRows <> 0) AND (NOT rs_vnumber.EOF))  
%>  
<p><%=PadMyNumber(rs_vnumber.Fields.Item("vnumber").Value)%>  
<%=(rs_vnumber.Fields.Item("vendorname").Value)%>  
<%=(rs_vnumber.Fields.Item("username").Value)%></p>  
<%  
Repeat1__index=Repeat1__index+1  
Repeat1__numRows=Repeat1__numRows-1  
rs_vnumber.MoveNext()  
Wend  
%>  

-=casey=-

[aspdaddy]

If you are using SQL server :
SELECT Right(Replicate('0',5) + <ColumnName>,5) from <Tablename>

Or if the column is a numeric type:

SELECT Right(Replicate('0',5) + convert(varchar(6),<ColumnName> ) ,5) from <Tablename>

[harrypsk]

A big thanks to both of you. I've been implementing the ASP route, but I'll definately try out the SQL route as well. It's people like you that have helped me out in so many situations. I'm self taught in web development, so I lack mad skills. I have recently gone back to school to learn some mad skills, so that like you, I can give back to the community one day instead of only asking questions.