• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1107
  • Last Modified:

SQL Server 2000 - force decimal value to 4 decimal places

Hi!  The update statement below is based on a text string from which I am extracting pieces that I need to load into a database.  I realized today that the value from characters 3 through 6 may have ending blanks rather than zeroes.  For example, sometimes the value is 3.423 without the ending 0.  This is causing the load to skip the value.  Is there a way to change the line below to force ending zeroes if the value is, for example, "3.4    " or "3.42  " or "3.423 "?  In these cases, I would want to have "3.4000", "3.4200" and "3.4230".  If the value is "3.423", that's fine and nothing needs to be done.
set newvalue = cast(substring(t1.Column1,3,6) as decimal(5,4))
Thanks,
Alexis
0
alexisbr
Asked:
alexisbr
  • 5
  • 4
  • 3
  • +3
3 Solutions
 
BrandonGalderisiCommented:
Try adding '000' to the end of the string before you substring.  This will only work though if you are loading it into a varchar field.  Trailing decimal zeros will be removed just like leading zeros for numeric (int/tiny/small) fields.

set newvalue = substring(t1.Column1 '000',3,6)
0
 
Kevin CrossChief Technology OfficerCommented:
Try using MONEY.

set newvalue = cast(substring(t1.Column1,3,6) as money)
0
 
Kevin CrossChief Technology OfficerCommented:
What are you storing this as?
The DECIMAL(5,4) should have worked now that I think about it.  MONEY will probably not show trailing zero where as the decimal should...your issue is probably the datatype of the column itself.  As Brandon said, if it is character, then you can add '000' to the end and take left 6 characters.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
alexisbrAuthor Commented:
Thanks for your responses.  
Brandon,
I am loading into a decimal field, not a varchar field.
set newvalue = cast(substring(t1.Column1,3,6) as decimal(5,4))

mwvisa1,
This is not a money value and the value must have 4 decimal places in the end.  Is there another way to handle this?

Thanks,
Alexis
0
 
SNilssonCommented:
Brandon is correct this is the way its stored and presented, if you need trailing zero's you have to convert to char and pad the extras on.
0
 
BrandonGalderisiCommented:
Ritesh.... That is what I posted.  and again, that won't work in numeric columns.
0
 
RiteshShahCommented:
yes Brandon, that's true. few more example in this link, especially Solution 3

http://blog.sqlauthority.com/2009/03/16/sql-server-udf-pad-ride-side-of-number-with-0-fixed-width-number-display/
0
 
alexisbrAuthor Commented:
Thanks, everyone.  Brandon, I thought about this some more.  What you are saying should work if I pad the string I have before doing the cast into the decimal field.  I will try that now.
Alexis
0
 
RiteshShahCommented:
Alexis,

What you think about this?



CREATE FUNCTION dbo.PadLeft
(@Value INT, @PadWidth INT, @PadChar CHAR(1))
RETURNS VARCHAR(255)
AS
BEGIN
RETURN ISNULL(REPLICATE(@PadChar, @PadWidth - LEN(@Value)), '')
+ CAST(@Value AS VARCHAR)
END
GO
-- Run following script to see output
SELECT dbo.PadLeft(123,7,0)
GO

Open in new window

0
 
alexisbrAuthor Commented:
Thanks, RiteshShah.  I changed your function to work the other side since I am padding right.  But it doesn't work with the decimal point.  My values will be .323 or .4354, etc.  I am just going to try to do something very simple to just add zeroes based on the length of the string I am working with.  I'll let you know how it goes.
Alexis
0
 
Anthony PerkinsCommented:
>>This is not a money value and the value must have 4 decimal places in the end.<<
Doe it really matter, if it is money or not?  If it gives you the right output surely that is what matters, right? (For the record money has 4 decimal places)

If you can change it to money, this is all you need and will give you the desired output:
CAST(varchar(20), YourColumnValue, 2)

In other words if 12.323 --> 12.3230

If you would rather not change it from decimal then use something like this:
CAST(varchar(20), CAST(YourColumnValue As money), 2)
0
 
Anthony PerkinsCommented:
Actually on second thoughts you can achieve the same with decimal, as in (where YoueColumnValue is defined as decimal(5,4) ):
CAST(varchar(20), YourColumnValue, 2)

If it is defined as varchar, then:
CAST(varchar(20), CAST(YourColumnValue as decimal(5,4)), 2)
0
 
BrandonGalderisiCommented:
The database will never store and extra zeros.  

What exactly isn't working that you need them?  Is this an application thing?  If so, leave it to your application to format the number how you desire.
0
 
alexisbrAuthor Commented:
Hi everyone,
Yes, the application is not formatting the number correctly and that will be resolved.  But, for now, we have users that need correct data so I had to do a quick fix from my end until the engineer who wrote the application that gives me the load data can fix his side.

I believe I have made the correct fix and will be testing it in a few minutes.  Someone is using the system now and creating more records to be loaded so I want to wait until they are done so I can test systematically without any new files coming in.

We have several databases with millions of records.  Thousands of records are added every 6 hours.  I wanted to avoid making any database changes if possible.
 
As soon as I am done testing, I will report back.  I believe you have all given me the info I needed to fix the problem.
Thanks,
Alexis
0
 
BrandonGalderisiCommented:
It's not a database issue though.  Trailing zeros on the decimal and leading zeros in the integer aren't maintained.  They are implied.  

ex.

1 = 0001 = 000000000000000000000000000000000000000000000000000000000000000000000000000000000001

Where does it stop?
0
 
alexisbrAuthor Commented:
Brandon,
Thanks for the info.  There is a lot more going on here than I have explained.  I only needed to get this one little piece working so I didn't get into all the details of this process.  

Thank you all for your help.  I am going to close this question as you have all answered my question.  I will open another question if I have further issues.
Thanks,
Alexis
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now