SQL help with zero's 2

[gbraden]

I am using SQL Server 2000. I am pulling year, month and count, but the query is returning value without leading zero (for Jan, return is 1 instead of 01)

I need to concatenate several values to form a key that will be used for calling up a record. The key is based on a type, year and counter, where the counter increments by type and month and resets with new month. I added 100 to counter the dropped zero, but when I convert datatype and use the RIGHT function, the zero is still dropped.

I have the following code:
Select msnIdentification, SequenceYear, SequenceMonth = (100 + SequenceMonth),
SequenceCount = (100 + SequenceCount), msnIdentification
+ convert(varchar(2), right(SequenceYear,2)) +
convert(varchar(2), right(SequenceMonth,2)) +
convert(varchar(3), right(SequenceCount,2))
from logSequenceCounter
go

which produces the following results:
HW 2007 103 101 HW0731
HW 2007 104 101 HW0741
IW 2007 101 102 IW0712
CW 2007 102 102 CW0722
IW 2007 103 103 IW0733
HW 2007 101 104 HW0714
HW 2007 105 106 HW0756
BW 2007 103 107 BW0737
IW 2007 104 110 IW07410
CW 2007 101 122 CW07122
IW 2007 102 122 IW07222
CW 2007 105 129 CW07529

What I am looking for is:
HW 2007 103 101 HW070301
HW 2007 104 101 HW070401
IW 2007 101 102 IW070102
CW 2007 102 102 CW070202
IW 2007 103 103 IW070303
HW 2007 101 104 HW070104
HW 2007 105 106 HW070506
BW 2007 103 107 BW070307
IW 2007 104 110 IW070410
CW 2007 101 122 CW070122
IW 2007 102 122 IW070222
CW 2007 105 129 CW070529

Thanks... I know the solution is simple, but I am not an application programmer, and simple would not apply with my experience.



Glen Braden

 

[gmmastros]

You need to add '00' to the value AFTER converting to varchar, and then take the right 2 characters. Like this..

[COLOR=blue]Select[/color] msnIdentification, 
       SequenceYear, 
       SequenceMonth = (100 + SequenceMonth), 
       SequenceCount = (100 + SequenceCount), 
       msnIdentification 
       + [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](2), [COLOR=#FF00FF]right[/color](SequenceYear,2)) 
       + [COLOR=#FF00FF]Right[/color]([COLOR=red]'00'[/color] + [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](2), SequenceMonth), 2)
       + [COLOR=#FF00FF]Right[/color]([COLOR=red]'00'[/color] + [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](3), SequenceCount), 2)
[COLOR=blue]from[/color] logSequenceCounter

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gbraden]

A quick and easy solution. My deepest thanks.


Glen Braden

 

[jensays]

I'm having a similar problem. I have 3 int fields. For two of them I need to add padded zeroes. I've tried to do an update on those fields, but nothing happens. This is my SQL:

update <file>
set <fieldname>= (RIGHT(REPLICATE('0',5)+CONVERT (varchar(5),<fieldname),5))
set <fieldname>= (RIGHT(REPLICATE('0',4)+CONVERT (varchar(4),<fieldname),4))
from <file>

When I do this as
SELECT RIGHT(REPLICATE('0',5)+CONVERT(varchar(5),<fieldname>),5)
FROM <file>
I do see the data padded, but I don't want to just see it that way, I want to actually change the data.

Obviously, I'm not the most experienced kid on the block .
What am I doing wrong here in the syntex?

 

[gmmastros]

There is nothing wrong with your syntax.

Since the field, in the table, is an integer field, it will NOT store the number padded with 0's. The only way to store the number padded with 0's is to change the field to varchar instead.

Now, don't misunderstand me. I am not suggesting that you actually change your data because this could have performance issues. Instead, I would suggest that you use a computed column. Then, when you want to display the number padded with 0's, use the computed column instead.

Something like this...

Alter Table [!]TableName[/!] Add [!]PaddedNumber[/!] As Right('00000' + Convert(VarChar(20), [!]NumberColumn[/!]), 5)

Now, when you do this...

Select top 10 * From [!]TableName[/!]

You will see a new column called PaddedNumber. This will have the same value as NumberColumn, but it will be padded with 0's.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I suppose I should explain a little bit more about computed columns. The data in the computed column is NOT stored in the database. It is calculated every time you use the column.

Why is this important? Because if you change the value of the integer column, the PaddedNumber column will automatically change. You don't have to do anything to make this happen.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jensays]

I ended up doing this task by creating a second (empty) table with the 2 int fields instead as nvarchar, and then was able to do an INSERT instead of the UPDATE. Perhaps not the shortest path, but at least it worked!