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

x
?
Solved

SQL Select Query for decimal Formatting

Posted on 2013-05-28
4
Medium Priority
?
403 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
[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
  • 3
4 Comments
 
LVL 9

Accepted Solution

by:
mimran18 earned 2000 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 49

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 49

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 49

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

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