Issue with leading zeros, decimal zeros and decima

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

Issue with leading zeros, decimal zeros and decima

Author

Topic

gtroya
Starting Member

3 Posts

Posted - 2009-03-17 : 16:20:53

I have a table that contains dollar amounts (positive and negative), the data type is float and this is what I need to accomplish:

The entire column needs to be converted to 10 digits for positive and negative amounts (including sign)

Sample of what I have:
0.10 and -0.10

Sample of what I need:
0000000010 and -000000010

for some reason I am missing the zero on some amounts like the sample above (0.10 - instead I have 0.1) and this caused problems so I changed the data type from Float to VARCHAR and then I added my decimal zeros (if needed)

I KNOW THIS IS NOT A DATA TYPE TO STORE AMOUNTS BUT I HAVE NOT FOUND ANY OTHER DT TO USE IT, I TRIED DECIMAL (9,2) BUT THE ZERO PROBLEM FOR THE AMOUNTS THAT END IN ZERO HAPPENED

One more thingm after I accomplished the zero decimal issue, I need to remove the decimal point

Sample of what I have to do:
0.10 changed to 010 and -010

Then I need to add leading zeron to these numbers for these formatting I am haveing difficulties to finish what i need

Please help or ask me any questions that I am sure you have
I t seems confused my explanation but I can clarify that for you

One more thing, I am new in SQL and I am using SQL 2000

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-17 : 16:33:03

Use the STR() function:

SELECT STR(myColumn, 10, 0) FROM myTable -- str(number, total digits, number of decimals)

This pads with spaces, if you need leading zeros:

SELECT replace(STR(myColumn, 10, 0), ' ', '0') FROM myTable

You definitely DO NOT want to store numerics as varchar.

gtroya
Starting Member

3 Posts

Posted - 2009-03-18 : 07:54:49

Good Morning and Thak you

The code works for positive numbers but left the negative ones like this

00000-1.63 and I need to look like this -000000163

What is the suggested data type I would need to accomplish this task?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 08:08:40

[code]DECLARE @Sample TABLE
(
i FLOAT
)

INSERT @Sample
SELECT 0.10 UNION ALL
SELECT -0.10 UNION ALL
SELECT -1.63

SELECT i,
CASE SIGN(i)
WHEN -1 THEN '-'
ELSE '0'
END + RIGHT(REPLICATE('0', 9) + CAST(ABS(100 * i) AS VARCHAR(9)), 9) AS Peso
FROM @Sample[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources