SQL Server????????????
????????????? ???????[ 2016/8/15 11:01:03 ] ????????????? SQL Server
?????????????@a??????
??????????????
???????????charindex?????????????right??2??????????????????char??1????????????????replace(@a??char(1) ??’a’)
???????????????char??0???????????????????????NULL???
????????????????y???????????????????????ó?????????????£??????????????п????и??????????????ο?
????loop
????UPDATE TblName SET
????ColName = LEFT(ColName??LEN(ColName)-1)
????WHERE ASCII(RIGHT( ColName ??1))=0
????end loop
???????湲???????????????????????????в?????????????????????á?
SET NOCOUNT ON
DECLARE @TblName VARCHAR(100)
DECLARE @UpdateString NVARCHAR(1000)
DECLARE @SelectString NVARCHAR(1000)
DECLARE @COlName VARCHAR(100)
DECLARE @COUNT INT
SET @TblName = '??????'--?????????????
--?????α??????????????????????VARCHAR??char??nVARCHAR?????????
DECLARE cur_ColName CURSOR
FOR
SELECT col.name
FROM syscolumns AS col
inner join sysobjects AS obj ON col.ID = obj.ID
INNER join systypes AS typ ON col.xtype = typ.xtype
WHERE obj.xtype ='U'
AND obj.name = @TblName
AND typ.name IN ('VARCHAR'??'CHAR'??'NVARCHAR'??'NCHAR')
FOR READ ONLY
--???α?
OPEN cur_ColName
FETCH NEXT FROM cur_ColName INTO @ColName
IF @@FETCH_STATUS0
BEGIN
PRINT '??ж???????Σ?'PRINT '????????????????' + @TblName + '???' PRINT '????????VARCHAR??CHAR??NVARCHAR??NCHAR???????Σ?' GOTO LABCLOSE
END--??????
WHILE @@FETCH_STATUS=0
BEGIN
--?????????
--??????????????
SET @SelectString = 'SELECT @COU=COUNT(*)
FROM ' + @TblName +'
WHERE ASCII(LEFT(' + @ColName +'??1))'+ @ColName + ' IS NOT NULL'
EXEC sp_executesql @SelectString??N'@COU INT OUTPUT'??
@COUNT OUTPUT WHILE @COUNT>0
BEGIN
SET @UpdateString =
' UPDATE ' + @TblName +
' SET ' + @ColName + '=RIGHT(' + @ColName + '??LEN(' + @ColName + ')-1)
WHERE ASCII(LEFT(' + @ColName + '??1))' + @ColName + ' IS NOT NULL'
EXEC sp_executesql @UpdateString
EXEC sp_executesql @SelectString??N'@COU INT OUTPUT'??@COUNT OUTPUT
END
--??????????????
SET @SelectString = 'SELECT @COU=COUNT(*)
FROM ' + @TblName +'
WHERE ASCII(RIGHT(' + @ColName +'??1))'+ @ColName + ' IS NOT NULL'
EXEC sp_executesql @SelectString??N'@COU INT OUTPUT'??
@COUNT OUTPUT WHILE @COUNT>0
BEGIN
SET @UpdateString = ' UPDATE ' + @TblName + ' SET '
+ @ColName + '=LEFT(' + @ColName + '??LEN(' + @ColName + ')-1)
WHERE ASCII(RIGHT(' + @ColName + '??1))' + @ColName + ' IS NOT NULL'
EXEC SP_EXECUTESQL @UpdateString
EXEC sp_executesql @SelectString??N'@COU INT OUTPUT'??
@COUNT OUTPUT
END
PRINT 'column: ' + @ColName + '??????ok'
FETCH NEXT FROM cur_ColName INTO @ColName
END
--????????α?
LABCLOSE: CLOSE cur_ColName
DEALLOCATE cur_ColName
GO
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11