Convert positive number to a negative

Question

Post reply

Convert positive number to a negative

aaa121

SSCertifiable

Points: 5902
More actions
October 16, 2007 at 5:37 am

#61388

Hi All,
How can you convert a positive number into a negative. I have tried *-1 but this doesn't seem to work.
Thanks

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

DECLARE @InitialNumber INT

DECLARE @OutputNumber INT

SET @InitialNumber = 9

SET @OutputNumber = ( CASE WHEN @InitialNumber > 0 THEN -@InitialNumber ELSE @InitialNumber END )

SELECT @InitialNumber AS Initial, @OutputNumber AS Final

Works like a charm....

--Ramesh

David Burrows SSC Guru Points: 65142 More actions · Answer 2

* -1 should work

what is the datatype and what makes you think it did not work

btw

preceding a column or variable with - will achieve the same, eg

DECLARE @myvariable int

SET @myvariable = 1

SELECT @myvariable,-@myvariable

Far away is close at hand in the images of elsewhere.
Anon.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

David Burrows (10/16/2007)
* -1 should work
what is the datatype and what makes you think it did not work
btw
preceding a column or variable with - will achieve the same, eg
DECLARE @myvariable int
SET @myvariable = 1
SELECT @myvariable,-@myvariable

I think David means ...

DECLARE @myvariable int

SET @myvariable = 1

SELECT @myvariable,@myvariable * -1

But BOTH work. It must be a another issue, but I don't think it's a dat-type issue. :hehe:

EDIT: Sorry Guys, I replied WAY to quick, and mis-read David's post

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

and if you have doubts, just put it between brackets.

SELECT @myvariable,@myvariable *(-1)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 5

I don't think braces would really matter. It should work even without braces. But as a safety you can try it with braces.

Prasad Bhogadi
www.inforaise.com

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

indeed.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Bob Fazio SSChampion Points: 10794 More actions · Answer 7

DECLARE @a INT

SELECT @a = 1

SELECT @a = -@a

SELECT @a

SELECT @a=-@a

SELECT @a

Note that @a*-1 doesn't change it unless you set it to itself.. Is that why you thought it didn't work?

Ram-425798 Right there with Babe Points: 798 More actions · Answer 8

Ram-425798

Right there with Babe

Points: 798

October 18, 2007 at 8:27 am

#743147

Ramesh!

Your answer is excellent!

Hats off!!!

Ram

S. Frog SSC Eights! Points: 886 More actions · Answer 9

Here's another way to always endup with a negative number (this works whether you start with a positive or a negative number):

DECLARE @a INT

SET @a = 1

SET @a = (-1)*sign(@a)*@a

Select @a

Kindest Regards,

--SF

(SELECT COALESCE(Phlogiston,Caloric,DarkMatter) as Construct FROM Science.dbo.Theory)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 10

Ram,

Its just the basic stuff, but anyhow thanks for appreciating....:):)

Adam,

Where R U?:hehe: Have you got what you wanted or you've found some other way around?

--Ramesh

SirSQLalot SSC Enthusiast Points: 167 More actions · Answer 11

Ramesh (10/16/2007)
DECLARE @InitialNumber INT
DECLARE @OutputNumber INT
SET @InitialNumber = 9
SET @OutputNumber = ( CASE WHEN @InitialNumber > 0 THEN -@InitialNumber ELSE @InitialNumber END )
SELECT @InitialNumber AS Initial, @OutputNumber AS Final
Works like a charm....

is there any advantage doing it that way verses

DECLARE @InitialNumber INT

DECLARE @OutputNumber INT

SET @InitialNumber = 9

SET @OutputNumber = -abs(@InitialNumber)

SELECT @InitialNumber AS Initial, @OutputNumber AS Final

the abs gives the absolute value of a number, which is ALWAYS positive, so then you just multiply it by -1 the execution times are almost exactly the same(7376 ms case vs 7200 ms abs) with a run of 100000 times each

If you haven't made it to PASS Summit, do so.
The knowledge and networking available there is amazing.
Bring a kilt.

S. Frog SSC Eights! Points: 886 More actions · Answer 12

D'oh!

Here I was all smug 😎 about: -1*sign(@a)*@a

when yours: (-1)*abs(@a)

is even more elegant. 🙂

Kindest Regards,

--SF

(SELECT COALESCE(Phlogiston,Caloric,DarkMatter) as Construct FROM Science.dbo.Theory)

David McFarland SSChampion Points: 11815 More actions · Answer 13

Keep in mind that many of these solutions handle negative numbers differently, so until the OP comes back to comment, there's no telling which method will work for him. He might not have any negative numbers to begin with, but if he does, then we definitely need to know whether his return values should all be negative, or just toggle the sign.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 14

David Easley (10/19/2007)
Ramesh (10/16/2007)
DECLARE @InitialNumber INT
DECLARE @OutputNumber INT
SET @InitialNumber = 9
SET @OutputNumber = ( CASE WHEN @InitialNumber > 0 THEN -@InitialNumber ELSE @InitialNumber END )
SELECT @InitialNumber AS Initial, @OutputNumber AS Final
Works like a charm....
is there any advantage doing it that way verses
DECLARE @InitialNumber INT
DECLARE @OutputNumber INT
SET @InitialNumber = 9
SET @OutputNumber = -abs(@InitialNumber)
SELECT @InitialNumber AS Initial, @OutputNumber AS Final
the abs gives the absolute value of a number, which is ALWAYS positive, so then you just multiply it by -1 the execution times are almost exactly the same(7376 ms case vs 7200 ms abs) with a run of 100000 times each

The only difference i can think of is that it looks simple to understand on first look itself...:hehe::hehe:

--Ramesh