Remove trailing zeros after decimal

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/

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.

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]

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources