Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2000 - force decimal value to 4 decimal places

Posted on 2009-05-04
17
Medium Priority
?
1,102 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 1200 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 400 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 400 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 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