• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

SQL Select Query for decimal Formatting

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
hojohappy
Asked:
hojohappy
  • 3
1 Solution
 
mimran18Commented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
& 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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now