Sql Server去除表中的不可見字元
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_STATUS<>0
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))<32
AND '+ @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))<32
AND ' + @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))<32
AND '+ @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))<32
AND ' + @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
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_STATUS<>0
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))<32
AND '+ @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))<32
AND ' + @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))<32
AND '+ @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))<32
AND ' + @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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-609100/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- C#移除字串中的不可見Unicode字元C#字串Unicode字元
- SQL server 中的臨時表SQLServer
- SQL Server中的臨時表和表變數SQLServer變數
- SQL Server安全審計中的常見疏忽NFSQLServer
- 如何去除特殊字元字元
- SQL Server中各個系統表的作用SQLServer
- 關於SQL Server中的字元儲存的問題的測試SQLServer字元
- SQL Server中row_number函式的常見用法SQLServer函式
- SQL Server中臨時表與表變數的區別SQLServer變數
- SQL Server複製的表中如何修改欄位SQLServer
- SQL Server不可以增加非空列SQLServer
- SQL Server 統計報表(不斷收藏中)SQLServer
- SQL Server中,WITH AS的使用SQLServer
- 不可見索引在表DML操作過程中依然被維護索引
- SQL SERVER臨時表的使用SQLServer
- SQL Server表分割槽SQLServer
- SQL server 分割槽表SQLServer
- sql server 修改表名SQLServer
- SQL server 聯表更新SQLServer
- Shell命令列中的特殊字元及其轉義(去除特殊含義)命令列字元
- Sql Server強制清空所有資料表中的記錄SQLServer
- SQL Server中合併使用者日誌表的方法SQLServer
- SQL Server常見問題收集SQLServer
- SQL Server中@@ROWCOUNT的用法SQLServer
- SQL server中的NULL值SQLServerNull
- SQL Server中distinct的用法SQLServer
- SQL Server中tempdb的管理SQLServer
- 關於SQL Server的分割槽表SQLServer
- SQL SERVER中SQL優化SQLServer優化
- Sql server 2005遷移至Oracle系列之三:在Oracle中建立sql中的常見函式SQLServerOracle函式
- SQL SERVER之分割槽表SQLServer
- SQL server 修改表資料SQLServer
- 利用DBCC PAGE檢視SQL Server中的表和索引資料SQLServer索引
- 在SQL Server 2005中實現表的行列轉換SQLServer
- SQL Server 2005 中的分割槽表和索引應用SQLServer索引
- SQL Server 2005中各個系統表的作用(轉)SQLServer
- 【INDEX】注意:不可見索引在表DML操作過程中依然被維護Index索引
- 常見 SQL Server 規範集錦SQLServer