广元信息网
历史
当前位置:首页 > 历史

怎样在SQL Server中去除表中不可见字符

发布时间:2019-09-13 20:35:49 编辑:笔名

以下的实例为去除指定表中数据类型是VARCHAR,CHAR,NVARCHAR的字段值中的不可见字符。

注释:此处只去掉前后的不可见字符,不包括中间的字符,而且没有区分中文。

在使用前需要指定对应要修改的表名,并且需要在对应数据库下执行;

SET NOCOUNT ONDECLARE @TblName VARCHAR(100)DECLARE @UpdateString NVARCHAR(1000)DECLARE @SelectString NVARCHAR(1000)DECLARE @COlName VARCHAR(100)DECLARE @COUNT INTSET @TblName = "YOURTABLENAME"--指定想要修改的表名--定义游标取出指定表内的数据类型是VARCHAR,char,nVARCHAR的字段名称DECLARE cur_ColName CURSORFORSELECT col.nameFROM syscolumns AS colinner join sysobjects AS obj ON col.ID = obj.ID INNER join systypes AS typ ON col.xtype = typ.xtypeWHERE obj.xtype ="U"AND obj.name = @TblNameAND typ.name IN ("VARCHAR","CHAR","NVARCHAR","NCHAR")FOR READ ONLY--打开游标OPEN cur_ColNameFETCH NEXT FROM cur_ColName INTO @ColNameIF @@FETCH_STATUS<>0BEGIN PRINT "没有对应表或字段,"PRINT "请确认当前数据库内有" + @TblName + "表," PRINT "或该表内有VARCHAR、CHAR、NVARCHAR、NCHAR类型的字段!" GOTO LABCLOSEEND--循环修改WHILE @@FETCH_STATUS=0BEGIN --拼修改字符串 --去掉左边的不可见字符 SET @SelectString = "SELECT @COU=COUNT(*) FROM " + @TblName +" WHERE ASCII(LEFT(" + @ColName +",1))<32AND "+ @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 @ColNameEND--关闭、释放游标LABCLOSE: CLOSE cur_ColName DEALLOCATE cur_ColName

热淋清颗粒的成份
婴儿手心出汗
五个月宝宝止咳方法
三个月婴儿咳嗽怎么办