表Table
字段:Bat
字段Bat内容如下格式:
ID Bat
1 Tom,Jack
2 Luck,Mary
要求实现:
ID Bat
1 Jack,Tom
2 Mary,Luck
即把Bat内容相互调换位置!.
要求用SQL实现....
另:如程序实现,请给出具体思路!
多谢1!
字段:Bat
字段Bat内容如下格式:
ID Bat
1 Tom,Jack
2 Luck,Mary
要求实现:
ID Bat
1 Jack,Tom
2 Mary,Luck
即把Bat内容相互调换位置!.
要求用SQL实现....
另:如程序实现,请给出具体思路!
多谢1!
ID,
Bat = PARSENAME(REPLACE(Bat, ',', '.'), 1)
+ ','
+ PARSENAME(REPLACE(Bat, ',', '.'), 2)
FROM(
SELECT ID = 1, Bat = 'Tom,Jack' UNION ALL
SELECT ID = 2, Bat = 'Luck,Mary'
)A-- 结果:
ID Bat
----------- -------------
1 Jack,Tom
2 Mary,Luck(2 行受影响)
INSERT INTO @tb
SELECT 1, 'Tom,Jack'
UNION ALL SELECT 2, 'Luck,Mary'UPDATE @tb
SET Bat = RIGHT(Bat,LEN(Bat) - CHARINDEX(',', Bat)) + ',' + LEFT(Bat, CHARINDEX(',', Bat) -1)SELECT * FROM @tb--结果
ID Bat
----------- ----------
1 Jack,Tom
2 Mary,Luck
(
id int,
bat varchar(30)
)insert into bat select 1, 'Tom,Jack'
insert into bat select 2, 'Luck,Mary'--语句
select id,right(bat,len(bat) - charindex(',',bat)) + ','+left(bat,charindex(',',bat) - 1)
from bat--结果
id
----------- -------------------------------------------------------------
1 Jack,Tom
2 Mary,Luck(所影响的行数为 2 行)