Solved

SQL Select Query for decimal Formatting

Posted on 2013-05-28
4
383 Views
Last Modified: 2013-06-14
I need a SQL Server Select Query to identify inproper decimal formatting.  I have a SQL Table with a column called PackageLenth.  This column should always have a value formatted like 999.9999.  Every value should have two digits following the decimal.  For example;

Good Values:
 1.25
 21.9011
 0.23

Bad Values:
9
3.4444454
5.5
0
Comment
Question by:hojohappy
  • 3
4 Comments
 
LVL 9

Accepted Solution

by:
mimran18 earned 500 total points
ID: 39202799
Here is the soltion

Create table tbl_Sample
(
 [Decimal] decimal(18,8)
)
GO

Insert into tbl_Sample Select 1.25 Union All
Select 21.9011 Union All
Select 0.23 Union All
Select 9 Union All
Select 3.4444454 Union All
Select 5.5


Select [Decimal]
,Case When LEN(Substring(Convert(varchar(max),Convert(float,[Decimal])),Charindex('.',[Decimal])+1,LEN([Decimal])))
<=1 Then 'Bad Values'

When LEN(Substring(Convert(varchar(max),Convert(float,[Decimal])),Charindex('.',[Decimal])+1,LEN([Decimal])))
>4 Then 'Bad Values'
Else 'Good Values' End As [Comments]

from tbl_Sample

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39203319
an integer is a valid entry into a decimal field, when you view it you can impose the number of decimals for display.

could I ask what the actual field definition is please? is it decimal? is it float? if it is decimal, and you only require 2 decimal places, then why not define it that way?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39203342
& here's another approach to the good/bad query
;with cte as (
              Select 1.25 as PackageLenth Union All
              Select 21.9011 Union All
              Select 0.23 Union All
              Select 9 Union All
              Select 3.4444454 Union All
              Select 5.5
              )
select
  str(PackageLenth,18,2) as forDisplay
, PackageLenth
, case when round(PackageLenth*100,0) = PackageLenth*100 then 'Good Value'
       else 'Bad Value'
  end as Quality
from cte

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39247711
conversion of numerics to varchar, then using string functions is unnecessarily complex and slower than just using a numeric function like round()

see: http://sqlfiddle.com/#!3/59e7c/2
oh well, I tried.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Square brackets 4 39
In or Between 2 44
Writing SQL Select Query result to a text file 12 27
SQL log file keeps growing despite getting successful log backups 4 34
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

12 Experts available now in Live!

Get 1:1 Help Now