hide the 0 in a formula - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

hide the 0 in a formula

Author

Topic

Danny1
Starting Member

1 Post

Posted - 2010-04-24 : 02:56:38

hello

in the results of a formula query I need to hide the 0.
For example 3,980 becomes 3,98 but 3,981 stay 3,981.
Someone can help me?

kind regards
Danny

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-24 : 03:10:20

SELECT REPLACE(field, '0', '') FROM tbl

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-24 : 03:35:39

ms65g.....and how do you think "101" would appear in your formula?

Danny....do mod 10 check incide a case clause
case
when mynumber mod 10 = 0 then mynumber / 10
when mynumber mod 10 <> 0 then mynumber
end as myadjustednumber

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-24 : 03:47:14

quote:
Originally posted by AndrewMurphy

ms65g.....and how do you think "101" would appear in your formula?

Danny....do mod 10 check incide a case clause
case
when mynumber mod 10 = 0 then mynumber / 10
when mynumber mod 10 <> 0 then mynumber
end as myadjustednumber

Your method is correct but need to modify little
if you are using sql server 2005
then use % instead of mod


case 
when mynumber % 10 = 0 then mynumber / 10
when mynumber % 10 <> 0 then mynumber
end as myadjustednumber

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-24 : 03:50:49

Or:

SELECT newFormat = CASE WHEN RIGHT(field, 1) = '0' THEN LEFT(field, LEN(field)-1) ELSE field END
FROM table_name

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-24 : 03:59:39

quote:
Originally posted by AndrewMurphy

ms65g.....and how do you think "101" would appear in your formula?

Danny....do mod 10 check incide a case clause
case
when mynumber mod 10 = 0 then mynumber / 10
when mynumber mod 10 <> 0 then mynumber
end as myadjustednumber

OP's sample data show me a string value not an integer value.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-24 : 04:04:30

quote:

OP's sample data show me a string value not an integer value.

yes solution may be chosen on the basis of datatype
but OP has not mentioned the datatype as such.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-26 : 04:24:26

quote:
Originally posted by Danny1

hello

in the results of a formula query I need to hide the 0.
For example 3,980 becomes 3,98 but 3,981 stay 3,981.
Someone can help me?

kind regards
Danny

This is the formation issue. If you use front end application, do the formation there

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-26 : 04:32:51

"OP's sample data show me a string value not an integer value"

3,980 is a number
"3,980" if written that way may lead you to think it was a string.

however my point still applies. replace (a,"0","") would replace ALL Zero's in the field, which was not the stated requirement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources