新增文章

文章标题分类内容

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 删除：函数

if exists ( select * from sys.objects where object_id = object_id(N'dbo.aGsf_Convert') and type in (N'FN',N'IF',N'TF') )
               drop     function                                       dbo.aGsf_Convert
   go
   
   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 创建：函数  aGsf_Convert  转换 全角半角

create function dbo.aGsf_Convert(
   
        @str   nvarchar(4000)   -- 要转换的字符串    
       ,@flag  bit              -- 转换成： 0 半角 1 全角    
   ) 
   returns  nvarchar(4000)    
   as 
   begin 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      declare  @pat   nvarchar(8),@step   int,@i   int,@spc   int

if @flag = 0

select @pat = N'%[！-～]%' ,@step = -65248 ,@str = replace( @str ,N'　' ,N' ' )    
      else    
         select @pat = N'%[!-~]%'   ,@step =  65248 ,@str = replace( @str ,N' ' ,N'　' )
        
      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

/*
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 使用示例
   
  declare  @s1 varchar(8000) = '中　２-３４５６a７８ＳＴＵＶabｎ中 国ｏｐｗｘｙｚ' ;

select  dbo.aGsf_Convert( @s1 ,0 ) 
         ,dbo.aGsf_Convert( @s1 ,1 )

全角字符 unicode 从 65281~65374  在顺序上是对应的
  半角字符 unicode 从    33~126

空格比较特殊,全角 12288 ,半角 32

指定排序规则 collate Latin1_General_BIN