CODEWITHSUNDEEP
sql-serverdecimal-point
Smudger
Smudger

Reputation: 10781

mssql display 1 decimal point if available, else show none

Simple one I think.

I have a query that shows lengths of items.

Query:

select length from vw_OutstandingVsInStock1 OVI
LEFT JOIN Departments DEP
on OVI.Department COLLATE DATABASE_DEFAULT=DEP.Description
where OutstandingVolume>0.39

This returns results like:

0.9
1.2
1.5
1.8
2.1
2.4
2.7
3.0
3.3
3.6...

In the case of 3.0 I want it to display as 3

so if no decimal value, show int with no decimals. if a decimal exists show decimal to 1 decimal point?

so desired output is 3 and 6 instead of 3.0 and 6.0

I am using MSSQL 2012.

Upvotes: 2

Views: 3225

Answers (4)

msi77
msi77

Reputation: 1632

Why not

select cast(length as float) from vw_OutstandingVsInStock1 OVI
...

Does it solve your problem?

Upvotes: 2

jpw
jpw

Reputation: 44881

I think this would work:

SELECT 
  CASE WHEN RIGHT(length,1) = 0 THEN STUFF(length, LEN(length)-1, 2, '') 
  ELSE STUFF(length, LEN(length), 0, '') 
  END

I assumed the length has data type numeric(?,1).

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453298

Another variant (Fiddle)

;WITH vw_OutstandingVsInStock1 (length)
     AS (SELECT 0.9 UNION ALL
         SELECT 1.2 UNION ALL
         SELECT 1.5 UNION ALL
         SELECT 1.8 UNION ALL
         SELECT 2.1 UNION ALL
         SELECT 2.4 UNION ALL
         SELECT 2.7 UNION ALL
         SELECT 3.0 UNION ALL
         SELECT 3.3 UNION ALL
         SELECT 3.6)
SELECT IIF(length % 1 = 0,
           CAST(length AS INT),
           CAST(length AS SQL_VARIANT))
FROM   vw_OutstandingVsInStock1

Upvotes: 5

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Can't think of anything cleaner than this at the moment. I've placed the literal values in a CTE, you would place your existing query there:

;With rowss(length) as (
select 0.9 union all select 1.2 union all select 1.5 union all select
       1.8 union all select 2.1 union all select 2.4 union all select
       2.7 union all select 3.0 union all select 3.3 union all select
       3.6
)
select
    STUFF(stLength,CHARINDEX('.',stLength),
        CASE WHEN stLength like '%.0' THEN 2 ELSE 0 END,'')
from
    (select CONVERT(varchar(10),length) as stLength from rowss) t

The trick is to use STUFF and to decide, based on how the string ends, to either delete 2 characters or do nothing.

Result:

0.9
1.2
1.5
1.8
2.1
2.4
2.7
3
3.3
3.6

Upvotes: 1

Related Questions