开发学院数据库MSSQL Server 删除数据库字段中的汉字或字符 阅读

删除数据库字段中的汉字或字符

 2009-09-05 00:00:00 来源:WEB开发网   
核心提示:删除汉字:CREATE FUNCTION DeleteHZ(@cargoname varchar(512))RETURNS varchar(512)ASBEGIN DECLARE @Result varchar(512) declare @sno smallint select @Result='' s

删除汉字:

CREATE FUNCTION DeleteHZ (@cargoname varchar(512)) 
RETURNS varchar(512) 
AS 
BEGIN 
   DECLARE @Result varchar(512) 
   declare @sno smallint 
   select @Result='' 
   select @sno=1  
   while(@sno<=datalength(@cargoname))   
   begin 
      if datalength(SUBSTRING(@cargoname,@sno, 1))=1 
         set @Result=@Result+SUBSTRING(@cargoname,@sno, 1) 
      set @sno=@sno+1 
   end 
   RETURN(@Result) 
END

select dbo.DeleteHZ(JobPosition) from PRC_PersonalInfo

---Result---

CUSTOMS     CUSTOMS報關員

删除字符:

CREATE FUNCTION DeleteEN  (@cargoname varchar(512)) 
RETURNS varchar(512) 
AS 
BEGIN 
   DECLARE @Result varchar(512) 
   declare @sno smallint 
   select @Result='' 
   select @sno=1  
   while(@sno<=datalength(@cargoname))  
   begin 
      if datalength(SUBSTRING(@cargoname,@sno, 1))=2 
         set @Result=@Result+SUBSTRING(@cargoname,@sno, 1) 
      set @sno=@sno+1 
   end 
   RETURN(@Result) 

替换SQL中的全角数字为半角数字

create function  Ufn_convertWideNumericToAnsi
(
 @vstrIn varchar(1000)
)
returns varchar(4000)
as
begin
 declare @strReturn varchar(4000)
  ,@bin  varbinary(4000)
  ,@str  varchar(4000)
  ,@stmp varchar(4)
  ,@i   int
  ,@len  int
--  ,@vstrIn varchar(1000)
--set @vstrIn ='031851001845'
 set @strReturn=''
 set @bin=convert(varbinary(4000),@vstrIn)
 exec master..xp_varbintohexstr @bin, @str out
 
 select @str=stuff(@str,1,2,'')
 set @len=len(@str)
 set @i=1
 while @i<@len
 begin
 set @stmp = substring(@str,@i,4)
 if(substring(@stmp,1,1) <> 'A')
  return @vstrIn
 set @stmp = replace(@stmp,'A','')
 set @stmp = replace(@stmp,'B','')
--print @stmp
 set @stmp = cast((convert(int,@stmp)-30) as varchar(1))
 set @strReturn = @strReturn + @stmp
  set @i=@i+4
 end
--print @strReturn
return  @strReturn
end

示例

select '031851001845' as ORG,  dbo.ufn_convertWideNumericToAnsi('031851001845') DES
ORG                      DES          
------------------------ ---------------------------
031851001845             031851001845

Tags:删除 数据库 字段

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接