Convert int to a fixed length str then...

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)

Convert int to a fixed length str then...

Author

Topic

claire99
Starting Member

17 Posts

Posted - 2003-03-18 : 15:51:42

I'd like to convert a int value to a fixed length str, if the int value short of the length then add 0 before it. Any idea how to do it?Thanks in advance!

-------------------
"And we know that all things work together for good to them that love God, to them who are the called according to his purpose." --- Roman 8:28

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-18 : 16:17:47

try:

Right('0000000' + convert(varchar(100), yourInt), YourLength)

Above, YourInt is the int, and YourLength is how long you want the final result to be. Make sure you have enough zeros in your string for your desired length ....

- Jeff

claire99
Starting Member

17 Posts

Posted - 2003-03-18 : 16:45:05

I got below error message:
"String or binary data would be truncated.
The statement has been terminated."
Any idea? Thanks.

-------------------
"And we know that all things work together for good to them that love God, to them who are the called according to his purpose." --- Roman 8:28

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-18 : 17:05:17

Right(replicate('0',20) + convert(varchar(20), yourInt), 20)

replace all the 20's with however long the string needs to be.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-18 : 20:16:11

There also the Str() function:

SELECT Replace( Str( myIntValue, 10, 0), ' ', '0')

The Str() function formats a number to a specified number of places and decimals (10 digits with 2 decimals, for example) and pads the left side with spaces so that the decimals align. If you want leading zero's, the Replace function changes the spaces to zero's. Doesn't work any better than the other solutions really, just another option for you.

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-03-19 : 05:02:16

Try this logic

declare @nozeros int
declare @data int
set @nozeros=6
set @data=1234
select stuff(replicate('0',@nozeros),@nozeros - len(@data) +1,@nozeros,@data)

with regards
V.Ganesh
vganesh@netassetmgmt.com
vganesh76@rediffmail.com

Enjoy working

claire99
Starting Member

17 Posts

Posted - 2003-03-19 : 08:33:47

Thanks all of your reply! I'd appreciate.
Actually "Right('0000000' + convert(varchar(100), yourInt), YourLength) ", "Right(replicate('0',20) + convert(varchar(20), yourInt), 20) " and "SELECT Replace( Str( myIntValue, 10, 0), ' ', '0') " are all work. The reason I got the error is there was a problem with the field size.
Thanks again, to all of you!
Have a nice day!

-------------------
"And we know that all things work together for good to them that love God, to them who are the called according to his purpose." --- Roman 8:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources