[RESOLVED] How to swap values in SQL Server 2008!

Hi…

I’m wondering if possible to swap values in database column using update script in sql server 2008!!
In this case I want to update all rows in one column by a single script.
For example if I have:

Table name: dbo.ABC
Column name: FGH
Rows value:
XXX 111 ZZZ 222
XXX 333 ZZZ 444
XXX 555 ZZZ 666
XXX 777 ZZZ 888

Rows value after swap update:
ZZZ 222 XXX 111
ZZZ 444 XXX 333
ZZZ 666 XXX 555
ZZZ 888 XXX 777

If possible ! how the script will be?

Regards…

Im not quite clear about the question. Is there one column with spaces in it or 4 separate columns? Assuming the latter it's quite easy:-

Code:

---

Update ABC
Set Col1 = Col3,
Col2 = Col4,
Col3 = Col1,
Col4 = Col2

---

Quote:

---

Originally Posted by FunkyDexter

Im not quite clear about the question. Is there one column with spaces in it or 4 separate columns? Assuming the latter it's quite easy:-

Code:

---

Update ABC
Set Col1 = Col3,
Col2 = Col4,
Col3 = Col1,
Col4 = Col2

---

---

Thanks for reply FunkyDexter :)

it's one column and the values in this column are:
XXX 111 ZZZ 222
XXX 333 ZZZ 444
XXX 555 ZZZ 666
XXX 777 ZZZ 888

* there are spaces between the values in this column

Code:

---

update ABC
set FGH = SUBSTRING(FGH, 9, 7) + ' ' + SUBSTRING(FGH, 1, 7)

---

Quote:

---

Originally Posted by Pradeep1210

Code:

---

update ABC
set FGH = SUBSTRING(FGH, 9, 7) + ' ' + SUBSTRING(FGH, 1, 7)

---

---

Many Thanks Pradeep1210 :wave:

It's working perfctly :)

But could you please explain the script :confused: if you have a time !

Regards...:)

The SUBSTRING function extracts the specified number of characters from the string.

The syntax is:
SUBSTRING ( expression, start, length)

You could google for SUBSTRING T-SQL command to know more about this function.

So the first substring extracts 7 characters from the field FGH starting from the 9th character. The second substring extracts 7 characters from the field FGH starting from the 1st character.

So this makes it (7 characters starting from 9th character) + (one space) + (7 characters starting from 1st character)

Hope you got it.

Pradeep :)

Thanks Pradeep for info about SUBSTRING function...:)

Best Regrads...:wave: