| Author |
Topic |
|
titanotam
Starting Member
6 Posts |
 Posted - 2009-07-08 : 03:36:25
|
| In my Select transaction, there are some values return "negative number", i want to convert them to number "0". Please show me how! Thanks a lot |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-08 : 03:42:20
|
replace(column,'negative number','0')If that is not what you mean then show example data please.Fred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
titanotam
Starting Member
6 Posts |
Posted - 2009-07-08 : 03:51:03
|
| i had a lot of "negative number" ( -12, -1, -6 ....) if i use replace(column,'negative number','0'), i only convert one "negative number" to zero. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-08 : 03:51:24
|
use case statementcase when col < 0 then 0 else col end
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-08 : 03:52:29
|
quote:
Originally posted by titanotam
i had a lot of "negative number" ( -12, -1, -6 ....) if i use replace(column,'negative number','0'), i only convert one "negative number" to zero.
your have series of negative number in a CSV string ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-08 : 03:54:01
|
select case when col < 0 then 0 else col end as col
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-08 : 03:55:41
|
This is going like ping pong when OP is not able to give more clear information i.e. sample data...Same old song all the time...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2009-07-08 : 03:55:41
|
| [code]select case sign(YourColumn) when -1 then 0 else YourColumn end [i] from ...[/code] |
|
|
|
titanotam
Starting Member
6 Posts |
Posted - 2009-07-08 : 04:05:42
|
| It works, thanks all |
|
|
|
|
Convert negative number to zero ?