Swap two integer numbers without using third variable in SQL Server.

Question

Khushi Singh · Answer

There are simple techniques to swap two integer numbers in SQL Server in which, third variable is not required including Addition and subtraction or in another way that is XOR. Here's how you can do it in SQL:

Using Addition and Subtraction:

-- DECLARE @a INT = 10, @b INT = 20;

-- Swap logic
SET @a = @a + @b; -- @a becomes 30 (10 + 20)
SET @b = @a - @b; -- @b becomes 10 (30 - 20)
SET @a = @a - @b; -- @a becomes 20 (30 - 10)

-- Result
SELECT @a AS A, @b AS B;

Explanation:

Add the two numbers and store the result in @a.
Subtract @b from the new @a and assign the result to @b. Now, @b holds the original value of @a.
Subtract the new @b (which holds the old value of @a) from the new @a. Now, @a holds the original value of @b.

Using XOR (Bitwise Operator):


DECLARE @a INT = 10, @b INT = 20;

-- Swap logic
SET @a = @a ^ @b; -- @a becomes 30 (binary XOR of 10 and 20)
SET @b = @a ^ @b; -- @b becomes 10 (XOR of 30 and 20)
SET @a = @a ^ @b; -- @a becomes 20 (XOR of 30 and 10)

-- Result
SELECT @a AS A, @b AS B;

Explanation:

XOR (^)function is then used to add the two numbers lying in @a.
The second XOR operation stores the value of the @a in an output buffer register @b.
The third XOR operation copies an original value of @b to @a.
Output:

After either method:

@a becomes 20.
@b becomes 10.
In both methods, the two variables are exchanged directly without the need to use a third variable.

forum

Swap two integer numbers without using third variable in SQL Server.

Ashutosh Patel

Can you answer this question?

1 Answers

Liked By