Solved

SQL Server 2000 - force decimal value to 4 decimal places

Posted on 2009-05-04
17
1,072 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
  • 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 59

Expert Comment

by:Kevin Cross
ID: 24296594
Try using MONEY.

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

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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now