Solved

SQL Server 2000 - force decimal value to 4 decimal places

Posted on 2009-05-04
17
1,080 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
Why do I get the message "Message has been thrown by target of an invocation"? 22 53
backup and restore 21 29
SQL, add where clause 5 23
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 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