商品信息下传到盘点机提示数据转换发生溢出

商品信息下传到盘点机报错

请备份数据库执行以下语句：
use hbfsv9
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 = 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_name = replace (item_name,’)’,’)’) where item_name 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’%！%’
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’%：%’
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’%｛%’
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’%，%’
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’%＃%’
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 = replace (item_name,'(‘,”) where item_name like’%(%’
update t_bd_item_info set item_subname= replace (item_subname,'(‘,”)where item_subname 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’%)%’
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’%（%’
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’%）%’

if object_id(N’u_convert’,N’FN’) is not null
drop function u_convert
GO
create function u_convert(
@str nvarchar(4000),
@flag bit
)
returns nvarchar(4000)
AS
begin
declare
@pat nvarchar(8),
@step int,
@i int,
@spc int
if @flag=0
begin
select @pat=N’%[！-～]%’,@step=-65248,
@str=replace(@str,N’　’,N’ ‘)
end
else
begin
select @pat=N’%[!-~]%’,@step=65248,
@str=replace(@str,N’ ‘,N’　’)
end
set @i=patindex(@pat collate LATIN1_GENERAL_BIN,@str)
while @i>0
select @str=replace(@str,
substring(
@str,@i,1),
nchar(unicode(substring(@str,@i,1))+@step)),
@i=patindex(@pat collate LATIN1_GENERAL_BIN,@str)
return(@str)
end
GO
—————————
–步骤2：修改全角调用函数
update t_bd_item_info set item_size= dbo.u_convert(item_size,0)
update t_bd_item_info set item_no= dbo.u_convert(item_no,0)
update t_bd_item_info set item_subno= dbo.u_convert(item_subno,0)
update t_bd_item_info set item_clsno= dbo.u_convert(item_clsno,0)
update t_pc_branch_price set item_no= dbo.u_convert(item_no,0)
update t_bd_item_barcode set item_no= dbo.u_convert(item_no,0)

update t_bd_item_info set item_name= SUBSTRING (item_name,0,20)
go
update t_bd_item_info set item_subname= SUBSTRING (item_subname,0,20)
go