PADDING 0

[lyudmila]

I have field in a table which has numeric(6,0)data type and has value range from 26 to 999999. I need to add leading zero's and have as finish result data type numeric(6,0). My values should be from 000026 to 999999
Example: 26 to 000026
154 to 000154
1008 to 001008
21009 to 021009
On old system it's done this way:

=zoned(substr(digits(itemnbr),1,5),6,0)
How I can do this in SQL Server?

 

[mwolf00]

SELECT REPLICATE('0',6-len(cast(pkID as varchar(6)))) & cast(pkID as varchar(6)) FROM myTable ---------------------------------------
HAPPY THANKSGIVING!!!!
mikewolf@tst-us.com

 

[lyudmila]

Hi Mike,
if I use your code my data type will be a CHAR(6),but I need NUMERIC(6,0). By the way to cancatenate these two expressions you need to use '+' not '&'.

 

[mwolf00]

print cast('000026' as numeric(6,0)) = 26
I thought you needed the result to be '000026'. A numeric datatype will not have leading zeros. You need a text string to have leading zeros, which is what my code gives you...


Sorry about the concatenation operation '&' vs '+' - I goofed on that one.
-----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[lyudmila]

Mike,
Thank you very much for your quick respond.