--合并数据库中的数据数据库中的数据(S2更改为S1)可能为:S2; S2,S1; S1,S2,S3; S1,S3,S2;
declare @City varchar(50)
declare @ID varchar(50)
declare @CityReplace varchar(50)
declare @strLen int
declare @strIndex int
declare @strSUIndex int
DECLARE AutoChangeCity CURSOR FOR
SELECT City,ID from Operator where City like '%S2%'
OPEN AutoChangeCity;
FETCH NEXT FROM AutoChangeCity into @City,@ID;
WHILE @@FETCH_STATUS = 0
BEGIN
set @strLen = len(@City)
set @strIndex = CHARINDEX('S2',@City)
set @strSUIndex = CHARINDEX('S1',@City)
--大于0表示存在S1和S2
if(@strSUIndex > 0)
BEGIN
--等于1即第一个;反之则是中间或最后一个。
if(@strIndex=1)
BEGIN
set @CityReplace = REPLACE (@City,'S2,','')
END
ELSE
BEGIN
set @CityReplace = REPLACE (@City,',S2','')
END
END
ELSE
BEGIN
set @CityReplace = REPLACE (@City,'S2','S1')
END
update Operator set City=@CityReplace where ID=@ID
FETCH NEXT FROM AutoChangeCity into @City,@ID;
END
CLOSE AutoChangeCity
DEALLOCATE AutoChangeCity