--您好：请备份客户的数据，执行如下语句，整理商品的规格，名称，简称，含有的特殊字符
--附加码与商品货号相同的商品：
if exists(select * from syscolumns where id=object_id('dbo.item_barcode_temp'))
drop table item_barcode_temp

select * into item_barcode_temp from t_bd_item_barcode where item_barcode in(select item_no from t_bd_item_info)
go
delete from t_bd_item_barcode where item_barcode in(select item_no from t_bd_item_info)
go
--下面语句是调整 简称或者是品名中有特殊字符的语句
go
update t_bd_item_info  set item_size   =  replace (item_size,'×','*') where item_size    like'%×%'
update t_bd_item_info  set item_name   =  replace (item_name,'×','*') where item_name    like'%×%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'×','*') where item_subname    like'%×%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'（','(') where item_size    like'%（%'
update t_bd_item_info  set item_name   =  replace (item_name,'（','(') where item_name    like'%（%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'（','(') where item_subname    like'%（%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'）',')') where item_size    like'%）%'
update t_bd_item_info  set item_name   =  replace (item_name,'）',')') where item_name    like'%）%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'）',')') where item_subname    like'%）%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'“','(') where item_size    like'%“%'
update t_bd_item_info  set item_name   =  replace (item_name,'“','(') where item_name    like'%“%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'“','(') where item_subname    like'%“%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'”',')') where item_size    like'%”%'
update t_bd_item_info  set item_name   =  replace (item_name,'”',')') where item_name    like'%”%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'”',')') where item_subname like'%”%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'“',')') where item_size    like'%”%'
update t_bd_item_info  set item_name   =  replace (item_name,'“',')') where item_name    like'%”%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'“',')') where item_subname like'%”%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'＋','+') where item_size    like'%＋%'
update t_bd_item_info  set item_name   =  replace (item_name,'＋','+') where item_name    like'%＋%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'＋','+') where item_subname like'%＋%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'３５','+') where item_size    like'%＋%'
update t_bd_item_info  set item_name   =  replace (item_name,'３５','+') where item_name    like'%＋%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'３５','+') where item_subname like'%＋%'
go
update t_bd_item_info  set item_size   =  replace (item_size,'℃','度') where item_size    like'%℃%'
update t_bd_item_info  set item_name   =  replace (item_name,'℃','度') where item_name    like'%℃%'
update t_bd_item_info  set item_subname   =  replace (item_subname,'℃','度') where item_subname    like'%℃%'
go
update t_bd_item_info set item_name= dbo.u_convert(item_name,0)  
update t_bd_item_info set item_subname= dbo.u_convert(item_subname,0)  
go
--处理名称有制表符tab 
update t_bd_item_info set item_name = replace(item_name,char(9),'') 
where charindex(char(9),item_name) > 0 
go
--处理名称有换行  
update t_bd_item_info set item_name = replace(item_name,char(10),'') 
where charindex(char(10),item_name) > 0 
go
--处理名称有回车  
update t_bd_item_info set item_name = replace(item_name,char(13),'') 
where charindex(char(13),item_name) > 0 
go
--处理名称的空格（前空格、后空格、所有空格）
update t_bd_item_info set item_name = replace(rtrim(ltrim(item_name)),' ','')  
where isnull(charindex(' ',item_name),0) > 0   
go
--处理名称的单引号 
update t_bd_item_info set item_name = replace(item_name,char(39),'') 
where charindex(char(39),item_name) > 0 
go
--处理名称的双单引号 
update t_bd_item_info set item_name = replace(item_name,char(34),'') 
where charindex(char(34),item_name) > 0 
go
--处理名称有制表符tab 
update t_bd_item_info set item_size = replace(item_size,char(9),'') 
where charindex(char(9),item_size) > 0 
go
--处理名称有换行  
update t_bd_item_info set item_size = replace(item_size,char(10),'') 
where charindex(char(10),item_size) > 0 
go
--处理名称有回车  
update t_bd_item_info set item_size = replace(item_size,char(13),'') 
where charindex(char(13),item_size) > 0 
go
--处理名称的空格（前空格、后空格、所有空格）
update t_bd_item_info set item_size = replace(rtrim(ltrim(item_size)),' ','')  
where isnull(charindex(' ',item_size),0) > 0   
go
--处理名称的单引号 
update t_bd_item_info set item_size = replace(item_size,char(39),'') 
where charindex(char(39),item_size) > 0 
go
--处理名称的双单引号 
update t_bd_item_info set item_size = replace(item_size,char(34),'') 
where charindex(char(34),item_size) > 0 
go
update t_bd_Item_info set item_subno = SUBSTRING(item_no,1,10) where item_subno = '0'
go
update t_bd_Item_info set item_name = SUBSTRING(item_name,1,20)
go
update t_bd_Item_info set item_subname = SUBSTRING(item_subname,1,10)
go
update t_bd_Item_info set item_subname = SUBSTRING(item_name,1,10) where item_subname = '' or item_subname is null
go
update t_bd_Item_info set item_subname= replace(item_subname,CHAR(10),'') where   item_subname like '%'+CHAR(10)+'%' 
go
update t_bd_Item_info set item_name= replace(item_name,CHAR(10),'') where   item_name like '%'+CHAR(10)+'%' 
go
update t_bd_Item_info set item_name= replace(item_name,',','') where   item_name like '%,%'
go
update t_bd_Item_info set item_subname= replace(item_subname,',','') where   item_subname like '%,%'
go
update t_bd_Item_info set item_size =  LOWER(item_size) 
go
update t_bd_Item_info set item_size =  replace(item_size,'—','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'】',']'),item_subname =  replace(item_subname,'】',']') 
go
update t_bd_Item_info set item_name =  replace(item_name,'【',']'),item_subname =  replace(item_subname,'【',']') 
go
update t_bd_Item_info set item_subname =  replace(item_subname,'.','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'+','') ,item_subname =  replace(item_subname,'+','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'-','') ,item_subname =  replace(item_subname,'-','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'*','') ,item_subname =  replace(item_subname,'*','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'/','') ,item_subname =  replace(item_subname,'/','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'（','') ,item_subname =  replace(item_subname,'（','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'）','') , item_subname =  replace(item_subname,'）','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'、','') ,item_subname =  replace(item_subname,'、','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'·','') ,item_subname =  replace(item_subname,'·','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'&','') , item_subname =  replace(item_subname,'&','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'’','') ,item_subname =  replace(item_subname,'’','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'《','') ,item_subname =  replace(item_subname,'《','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'》',''), item_subname =  replace(item_subname,'》','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'“','') ,item_subname =  replace(item_subname,'“','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'”',''), item_subname =  replace(item_subname,'”','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'%','') , item_subname =  replace(item_subname,'%','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'＠','') , item_subname =  replace(item_subname,'＠','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'＃','') , item_subname =  replace(item_subname,'＃','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'～','') , item_subname =  replace(item_subname,'～','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'￥','') , item_subname =  replace(item_subname,'￥','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'·','') ,item_subname =  replace(item_subname,'·','') 
go
update t_bd_Item_info set item_name =  LOWER(item_name) ,item_subname =  LOWER(item_subname)
go
update t_bd_Item_info set item_name =  replace(item_name,'。','') ,item_subname =  replace(item_subname,'。','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'‘','') ,item_subname =  replace(item_subname,'‘','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'、','') ,item_subname =  replace(item_subname,'、','') 
go
update t_bd_Item_info set item_name =  replace(item_name,'—','') ,item_subname =  replace(item_subname,'—','') 
go