Alternate the value of 2 variable without using temporary variable

Question

Alternate the value of 2 variable without using temporary variable

Teemo

Ten Centuries

Points: 1376
More actions
July 19, 2010 at 8:54 pm

#97321

Hi guys,
I have this statement,
DECLARE @Var1 varchar(2),
@Var2 varchar(2)
SET @var1='A'
SET @var2='B'
I have to put the value of var2 in var1 without using another variable.
And so the output will be
var1='B'
var2='A'
Is there a way in SQL Server 2000 to achieve this?, I mean is there a ready made function to achieve this?
Thank you in advance

Viewing 11 posts - 1 through 10 (of 10 total)

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

sql-programmers SSC-Addicted Points: 494 More actions · Answer 1

As per my assumption there is no possibility of swapping the variable values with out using temp variables in SQL 2000. If it is 2 columns then this is possible, check it

create table T(Val1 char(1),Val2 char(1))

insert into T values('A','B')

update T set Val2=Val1,Val1=Val2

select * From T

SQL Sever Performance tuning

Answer 2

Hi,

I findout solution for interchange the variable without Temp variable.

Pls see below solution helpfull for you

DECLARE @Var1 varchar(max),@Var2 varchar(max)

SET @var1='Sethuraj'

SET @var2='Sahithya'

SELECT SUBSTRING(@var1+@var2,CHARINDEX(@var2,@var1+@var2),LEN(@var1+@var2))

SELECT SUBSTRING(@var2+@var1,CHARINDEX(@var1,@var2+@var1),LEN(@var2+@var1))

PRINT @var1

PRINT @var2

'Strength is Life'

'Weakness is Death'

- Swami Vivekananda

Answer 3

Hi,

I findout solution for interchange the variable without Temp variable.

Pls see below solution helpfull for you

DECLARE @Var1 varchar(max),@Var2 varchar(max)

SET @var1='Sethuraj'

SET @var2='Sahithya'

SELECT SUBSTRING(@var1+@var2,CHARINDEX(@var2,@var1+@var2),LEN(@var1+@var2))

SELECT SUBSTRING(@var2+@var1,CHARINDEX(@var1,@var2+@var1),LEN(@var2+@var1))

PRINT @var1

PRINT @var2

'Strength is Life'

'Weakness is Death'

- Swami Vivekananda

sql-programmers SSC-Addicted Points: 494 More actions · Answer 4

Hi Sethuraj,

Check your reply, the variables still having the old values

DECLARE @Var1 varchar(100),@Var2 varchar(100)

SET @var1='Sethuraj'

SET @var2='Sahithya'

select @var1,@var2

SELECT SUBSTRING(@var1+@var2,CHARINDEX(@var2,@var1+@var2),LEN(@var1+@var2))

SELECT SUBSTRING(@var2+@var1,CHARINDEX(@var1,@var2+@var1),LEN(@var2+@var1))

select @var1,@var2

SQL Sever Performance tuning

Teemo Ten Centuries Points: 1376 More actions · Answer 5

Hi Sethuraj, The code is working fine, But technically it did not interchange the value of the variable.

DECLARE @Var1 varchar(10),@Var2 varchar(10)

SET @var1='A'

SET @var2='B'

SELECT SUBSTRING(@var1+@var2,CHARINDEX(@var2,@var1+@var2),LEN(@var1+@var2)) AS 'A'

SELECT SUBSTRING(@var2+@var1,CHARINDEX(@var1,@var2+@var1),LEN(@var2+@var1)) AS 'B'

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 6

I did it by using ur example. Check whether this what you are looking for.....

DECLARE @Var1 varchar(max),@Var2 varchar(max)

SET @var1='Sethuraj'

SET @var2='Sahithya'

PRINT '@var1 = ' + @var1

PRINT '@var2 = ' + @var2

set @var1 = @var1+@var2

set @var2 = substring(@var1, 1, charindex(@var2, @var1)-1)

set @var1 = substring(@var1, len(@var2)+1, len(@var1))

PRINT '@var1 = ' + @var1

PRINT '@var2 = ' + @var2

Teemo Ten Centuries Points: 1376 More actions · Answer 7

Thanks sumit.agrawal, This is what im looking for, but one last thing, what is the purpose of CHARINDEX?

Answer 8

Hi,

Sorry for the delay.yeah i saw my problem any have u got solution.

i give one example for charindex here,

select charindex('t','sethuraj')

First Parameter - Return the position of given char t,

Second Parameter - Whole string (ex:sethuraj)

Answer 9

sethuraj, (From Sivakasi,TamilNadu,India)

SSC Veteran

Points: 225

July 20, 2010 at 1:02 am

#1195313

Hi sumit.agrawal,

Thanks for your correction.

Teemo Ten Centuries Points: 1376 More actions · Answer 10

Teemo

Ten Centuries

Points: 1376

July 20, 2010 at 1:08 am

#1195315

thanks for all the info 🙂