String Conversion Question - 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

General SQL Server Forums

New to SQL Server Programming

String Conversion Question

Author

Topic

apantig
Posting Yak Master

104 Posts

Posted - 2006-11-07 : 02:26:42

Hi guys,

How will I convert this into a string type with Zero Padding on the left?

Say I have an amount of 12345.89, then the result I like is this:
000000001234589

Please help me.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 02:33:02

[code]declare @s decimal(10,4)

select @s = 12345.89

select right(replicate('0', 20) + convert(varchar, cast(100 * @s as bigint)), 15)[/code]

Peter Larsson
Helsingborg, Sweden

apantig
Posting Yak Master

104 Posts

Posted - 2006-11-07 : 02:34:47

wow,

that's nice. it works, thank you. but "bigint" does not work in my SQL 7. I used "int" only.

Thank you

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-07 : 02:34:51

Declare @float float
Set @float = 12345.89

Select '00000000' +Replace(Cast(@Float as varchar(20)),'.','')

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-07 : 02:37:06

[code]select replace(str(cast(12345.89 * 100 as int), 15, 0), ' ', '0')[/code]

Harsh Athalye
India.
"Nothing is Impossible"

apantig
Posting Yak Master

104 Posts

Posted - 2006-11-07 : 02:41:59

Guys,

I love you for that !!!!

You all have very good suggestions. That's is why I love SQL Forum. You guys are very intelegent

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 02:46:01

This have no boundaries, such as converting to INT (which is max 10 chars)

declare @s decimal(10,4)

select @s = 12345.89

select replace(str(100 * @s, 15, 0), ' ', '0')

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-09 : 10:33:55

If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources