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

[HOTFIX]

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…

[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:-

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

[HOTFIX]

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:-

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

[Pradeep1210]

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

[HOTFIX]

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

Many Thanks Pradeep1210

It's working perfctly

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

Regards...

[Pradeep1210]

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

[HOTFIX]

Thanks Pradeep for info about SUBSTRING function...

Best Regrads...