SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Remove trailing zeros after decimal
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
310 Posts

Posted - 02/13/2014 :  15:35:06  Show Profile  Reply with Quote
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

USA
35953 Posts

Posted - 02/13/2014 :  16:54:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Starting Member

Germany
35 Posts

Posted - 02/14/2014 :  09:14:47  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
279 Posts

Posted - 02/14/2014 :  17:47:10  Show Profile  Reply with Quote


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

Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1215 Posts

Posted - 02/17/2014 :  00:12:25  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 02/20/2014 :  08:22:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000