Solved

SQL Server 2000 - force decimal value to 4 decimal places

Posted on 2009-05-04
17
1,088 Views
Last Modified: 2012-05-06
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
Comment
Question by:alexisbr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
17 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 300 total points
ID: 24296593
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24296594
Try using MONEY.

set newvalue = cast(substring(t1.Column1,3,6) as money)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24296634
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:alexisbr
ID: 24296657
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
 
LVL 8

Expert Comment

by:SNilsson
ID: 24296831
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24296927
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24296978
Ritesh.... That is what I posted.  and again, that won't work in numeric columns.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24297030
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
 

Author Comment

by:alexisbr
ID: 24297116
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
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 100 total points
ID: 24297126
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
 

Author Comment

by:alexisbr
ID: 24297235
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 24297662
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24297976
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24297985
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
 

Author Comment

by:alexisbr
ID: 24298072
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24298425
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
 

Author Comment

by:alexisbr
ID: 24298495
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question