Prefilling a numeric field with zeros in SQL

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

SQL Server 2000 Forums

SQL Server Development (2000)

Prefilling a numeric field with zeros in SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-21 : 07:53:17

mamta writes "I have a table in SQL server which is then exported as a flat file for mainframe process. I need to have one of the numeric field prefilled with zeros. PS the field length has to be 15 characters long. ANy help is appreciated."

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-21 : 08:20:20

Select RIGHT('000000' + IsNull(CAST(MyIntCol as VARCHAR), 6), '')

This will create a 6 digit integer with leading zeros.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-21 : 08:21:28

Try SELECT RIGHT('000000000000000' + MyIntColumn) FROM TableName

Owais

edit:

Make it idiot proof and someone will make a better idiot

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-21 : 10:50:34

Owais!


DECLARE @MyIntColumn int

SELECT @MyIntColumn = 1

SELECT RIGHT('000000000000000' + @MyIntColumn) -- Still needs the second parameter

SELECT RIGHT('000000000000000' + @MyIntColumn, 15) -- Seems to be an implicit conversion to int


SELECT RIGHT(REPLICATE('0',15)+CONVERT(varchar(15),@MyIntColumn),15)

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-21 : 11:09:27

Do they have an icon for dumb yak?

duh...not enough coffee today...

been on the road, drove at least 100 miles today in crazy traffic!

Owais

Make it idiot proof and someone will make a better idiot

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-21 : 12:32:55

The only thing I dislike about this site is the YAK business. Could someone explain to me what this is all about.

YAK SQL.... what the...?!

__________________
Make love not war!

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-10-21 : 22:57:53

If that's the only thing you dislike we must be doing something right! Me? I'm getting less and less fond of the blue all over the site. Blue, blue blue.

But ... back to the yak! Obviously you haven't fully considered the myriad of benefits associated with the yak -- commonly known as the Bison of Tibet. Maybe a quick perusal of the yak section on the site will help you "back the yak".

http://www.sqlteam.com/FilterTopics.asp?TopicID=148

After all, how many technical sites have an official mascot? Well, ok, barely semi-offical at best. I am always curious what people think when the occasional yak link pops into their search result. On the bright side if you were to search Google on "sql server and yak" we're first! It's good to be first.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-22 : 02:44:30

Yikes...Graz! the last two links on our beloved yak page don't work!!

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources