Improve company productivity with a Business Account.Sign Up

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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