Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • 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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
& 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
 
PortletPaulCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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