Leading zeros? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Leading zeros?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-17 : 15:30:36

I'm wondering if anyone knows of a way to get a textbox to have leading zeros for a value. What I'm trying to do is pull a sequential number from within a sql database, but wanted to be able to have leading zeros for that value.

For example: The value I'm pulling is 149, I want to be able to pull that from the database with 5 decimal and leading zeroes.

So it will look like 00149 instead of 149. Later if the number goes up to 3443, I want it to be able to be 5 decimal with leading zeroes so it will be 03443 like that.

Any suggestions or code examples?

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 15:34:29

DECLARE @x int
SELECT @x = 7
SELECT RIGHT(REPLICATE('0',3)+CONVERT(varchar(3),@x),3)

Brett

8-)

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-17 : 16:09:31

Thanks Brett. You've been a life saver.

I have no idea where to put that code? Here's is an example of my query.

Dim strNextNumber As String = "SELECT NEXT_NUMBER " _
& "FROM NEXT_NUMBER_GEN " _
& "WHERE TABLE_NAME='QUOTE'"

Do I just add that quote as part of the query or do I need to create a stored procedure? It's only a small query retrieving one field.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-17 : 16:48:17

You put it in a stored procedure. Regardless of how small the query is, you should be using stored procedures. I believe this has been mentioned a few times before.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-17 : 16:52:00

formatting the leading zeroes in ASP.NET is really, really easy to do. you should just return the value from SQL Server, and format the result at the presentation layer. If you store the integer in a variable, the integer class has a ToString() method that will allow you to format it very easily with leading zeroes.

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-17 : 21:04:32

We do this

DECLARE @x int
SELECT @x = 149
SELECT RIGHT('00000'+LTRIM(@x),5)

Jeff, I agree, sometimes though when we give the PL guys a sproc,
we just tell them: "use this, and don't worry 'bout a thing

"
.............

rockmoose

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-18 : 09:58:28

Tara, I know this has been mentioned everytime I post a question and I provide a query within the asp page. It's just so convenient and bad habit for me just to create a query within my codes on the fly.

Jeff can you elaborate on how to format this within the asp.net layer?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-18 : 10:04:05

I'm reading this posting and looking at Brett's example:

DECLARE @x int
SELECT @x = 7
SELECT RIGHT(REPLICATE('0',3)+CONVERT(varchar(3),@x),3)

and the Moose's example:

DECLARE @x int
SELECT @x = 149
SELECT RIGHT('00000'+LTRIM(@x),5)

and I'm thinking to myself--"What's the difference?"

Did I miss something here?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-18 : 11:41:39

>> Did I miss something here?
Would it not be faster just to try them...

The diff is that one does 007 and one 00007
Just a number thingy.

rockmoose

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-18 : 13:25:59

quote:
Originally posted by rockmoose

>> Did I miss something here?
Would it not be faster just to try them...
The diff is that one does 007 and one 00007
Just a number thingy.

rockmoose

Well, 'Moose, I guess what I'm alluding to is that why should anyone write the extra coding: SELECT RIGHT(REPLICATE('0',3)+CONVERT(varchar(3),@x),3) when you can do the same thing with less: SELECT RIGHT('00000'+LTRIM(@x),5)?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-18 : 13:49:12

With some help I got it to work now.

Dim i As Integer, j As String, k As String, val As String

i = txbQuoteID.Text
k = "00000"
j = Convert.ToString(i)
val = IIf(j.Length < 5, k.Substring(1, (5 - j.Length)) & j, j)

txbQuoteID.Text = val

So this will display the value with leading zeros. Thanks to all who had helped out.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-18 : 14:02:46

Chris -- you have a great talent for overcomplicating things!

All joking aside, overcomplicating is one of the worst things you can do as a programmer. Your code will become unreadable and unmanageable over time. Keep it simple.

I think in your case all you need is 1 line:

txbQuoteID.Text = CInt(txbQuoteID.Text).ToString("00000")

The ToString() method, which you are using in your code, is much more flexible than what you are using it for. Whenever you use a method, be sure to read all of the options available for it and experiement to find the simpliest way to accomplish what you need. Also, the CInt() function converts your text string to an integer.

(be sure to add error checking as well if it is possible a non-numeric value gets entered in txbQuoteID.Text or your application will crash).

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-18 : 14:15:44

LOL!!!!

I wish that wasn't true about my talent, but without much experience I guess that's the case. I played around a bit and got it to work, but yes there are always better method.

Thanks for the example, it now works and much shorter than what I had before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources