数据库使用技巧――SQL 全角与半角切换_MySQL

bitsCN.com

    数据库系统中，经常有些用户在输入数据的时候会不小心使用全角输入，这就有可能会导致我们的程序出错，如何解决此类问题了。

测试代码：

select cast('111' as int) as num1

select cast('111' as int) as num2

运行结果：

第一个正确显示: 111

第二个则报错: 在将 varchar 值 '111' 转换成数据类型 int 时失败。

下面使用自定义标量函数来解决这个问题：

          if object_id(N'u_convert',N'FN') is not null
 drop   function u_convert
GO

转换原理

全角字符unicode编码从65281~65374

半角字符unicode编码从33~126

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

而且除空格外,全角/半角按unicode编码排序在顺序上是对应的

所以可以直接通过用+-法来处理非空格数据,对空格单独处理

like的时候,指定排序规则 COLLATE Latin1_General_BIN

是保证字符顺序按unicode编码排序

          */ 
create   function   u_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
     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

测试语句：

select cast('111' as int) as num1

select cast('111' as int) as num2

运行结果：

第一个正确显示: 111

第二个则报错: 在将 varchar 值 '111' 转换成数据类型 int 时失败。

下面使用自定义标量函数来解决这个问题：

       if object_id(N'u_convert',N'FN') is not null
 drop   function u_convert
GO

转换原理

全角字符unicode编码从65281~65374

半角字符unicode编码从33~126

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

而且除空格外,全角/半角按unicode编码排序在顺序上是对应的

所以可以直接通过用+-法来处理非空格数据,对空格单独处理

like的时候,指定排序规则 COLLATE Latin1_General_BIN

是保证字符顺序按unicode编码排序

       */ 
create   function   u_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
     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

测试语句：