No decimals and leading zeros - 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

No decimals and leading zeros

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-07 : 22:14:43

Can someone tell me how to convert a dollar amount to a value with no decimals please? Example: I would like 10,000.00 be 0001000000 with leading zeros.

Also I have a rate of 0.0131 that I need with no decimals

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-08 : 00:39:29

declare @val decimal(18,4)
set @val = 10000.9885
select parsename(@val,2)+''+parsename(@val,1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 01:57:04

is it always three 0's before? then use

select '000'+replace(@val,'.','')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-08 : 02:21:06

quote:
Originally posted by rjackman1959

Can someone tell me how to convert a dollar amount to a value with no decimals please? Example: I would like 10,000.00 be 0001000000 with leading zeros.

Also I have a rate of 0.0131 that I need with no decimals

Where do you want to show formatted numbers?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 02:22:18

SELECT REPLACE(STR(100 * @DollarAmount, 0, 10), ' ', '0')

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-08 : 07:51:12

Thanks for all the help, but I'm not quite there yet.
replace(MyField,'.','') works, but I need the leading 0’s. I have a dollar amount “MyField” that’s not always the same length. lets say 125.25, but I want the output to be 00012525.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-08 : 07:56:24

Have you tried Visakh's solution dated 01/08/2009 : 01:57:04 ??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 07:56:47

Here we go again.

SELECT REPLACE(STR(100 * MyField, 0, 10), ' ', '0')
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-08 : 08:22:38

Sorry for the inexperience. I don't think Visakh's solution will work because it is not always 3 zeros at the beginning and I tried REPLACE(STR(100 * MyField, 0, 10), ' ', '0'), but it came back with NULL when it should be 000000012263.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 08:29:11

quote:
Originally posted by rjackman1959

Sorry for the inexperience. I don't think Visakh's solution will work because it is not always 3 zeros at the beginning and I tried REPLACE(STR(100 * MyField, 0, 10), ' ', '0'), but it came back with NULL when it should be 000000012263.

then tell us the rule for number of leading 0's to be appended. We cant guess what you're trying to achieve.with some sample data, show what you want

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:31:18

rjackman1959, now you have 12 digits in your last post. Don't you think it's appropriate to also output 12 digits, instead of the suggested 10 digits that was based on your original post?

To your defense it was my bad with previous example. I switched places for Length, and decimals parameters.
Checking Books Online gives this

SELECT REPLACE(STR(100 * MyField, 12, 0), ' ', '0')
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-08 : 08:42:11

Thanks Peso that was perfect. Man is it always a war zone in here? I should have been more specific with the length. This is the beginner’s forum isn't it?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-08 : 08:44:24

sounds like you want a pad left, something like this


right(replicate('0',12)+ rtrim(ltrim(YourColumn)),12)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:45:54

Yes. It is the beginners forum.

And you get answer quality for what you ask.
If you supply the wrong data, our answers will never be more accurate than that.

And supplying the correct is not a beginner thing, is it?
I know we all can make mistake (I did when providing parameters in wrong order).

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-08 : 09:07:46

OK sorry, but now I'm trying the same thing with a rate field. The field is 8 digits long and this is what a normal select gives me "0.0131". I would like it to do the same as above, but only gime me 8 digits. I tried Peso's solution that worked perfect on the example above, but it geves me "00000001" for this output.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:16:05

You may want to multiply with 10,000 instead of 100?
Or increase the number of decimals?

SELECT REPLACE(STR(100 * MyRate, 8, 2), ' ', '0'),
REPLACE(STR(10000 * MyRate, 8, 0), ' ', '0')
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-08 : 09:32:34

Perfect. Thank You Peso

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources