Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Remove trailing zeros after decimal

Author  Topic 

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-02-13 : 15:35:06
Hi friends,

I have a numeric column which when dispalyed in aresult set shows like this

Customer_Score
1.0
3.5
2.0
4.8


I want to show this in the following way

1
3.5
2
4.8

How can i achieve this thank you in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-13 : 16:54:25
Do the formatting in your application, not in SQL. This is a presentation layer issue and should not be handled in the database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-02-14 : 09:14:47
Not tested, hope this helps.

case when Customer_Score like '%.0'
THEN left(Customer_score,1)
Else Customer_Score
End as Customer_Score

Marcus

I learn something new everyday.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-02-14 : 17:47:10
[code]

SELECT REPLACE(CAST(Customer_Score AS varchar(10)), '.0', '') AS Customer_Score
FROM (
SELECT CAST(1.0 AS decimal(9, 1)) AS Customer_Score UNION ALL
SELECT 3.5 UNION ALL
SELECT 2.0 UNION ALL
SELECT 4.8
) AS test_data

[/code]
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2014-02-17 : 00:12:25
You would be wiser to follow tkizer's advice, it is a much better practice.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2014-02-20 : 08:22:30
this is the other way to do it

DECLARE @tab Table (VAL VARCHAR(10))
INSERT INTO @tab VALUES ('1.0')
INSERT INTO @tab VALUES ('1.0')
INSERT INTO @tab VALUES ('3.5')
INSERT INTO @tab VALUES ('4.5')
select CASE WHEN VAL LIKE '%.0%' THEN REPLACE(CAST(VAL AS DECIMAL(9,1)),'.0',' ') ELSE VAL END AS VAL from @tab

P.V.P.MOhan
Go to Top of Page
   

- Advertisement -