sql 实现全角与半角字符转换的处理函数

最新推荐文章于 2021-06-17 10:05:21 发布

fayxue

最新推荐文章于 2021-06-17 10:05:21 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/fayxue/article/details/2806225

本文介绍了一个SQL Server中的自定义函数f_Convert，用于将全角字符转换为半角字符或反之。该函数通过Unicode值进行转换，并提供了一个批处理脚本，用于批量更新数据库表中的全角字符。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--2 实现全角与半角字符转换的处理函数
CREATE FUNCTION f_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
GO

declare T_cursor cursor local for
select
    a.Name,    b.Name
from
    sysobjects a
join
    syscolumns b on a.ID=b.ID
join
    systypes c on c.xusertype=b.Xtype
where
    a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
    exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0'
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor

---------------------------------------

--改列的数据全角为半角
declare T_cursor cursor local for
select
    a.Name,    b.Name
from
    sysobjects a
join
    syscolumns b on a.ID=b.ID
join
    systypes c on c.xusertype=b.Xtype
where
    a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
    exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0')--少了)
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor
go
--改列名全角为半角
declare T_cursor cursor local for
select
    a.Name,    b.Name
from
    sysobjects a
join
    syscolumns b on a.ID=b.ID
where
    a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
    exec('exe sp_rename '''+@tabName+'.'+@ColName+''',''dbo.f_Convert('+@ColName+',0)''')
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor

------------------------------------------------------
--改列名全角为半角
declare T_cursor cursor local for
select
    a.Name+'.'+b.Name,NameNew=dbo.f_Convert(b.Name)
from
    sysobjects a
join
    syscolumns b on a.ID=b.ID
where
    a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0
declare @tabName sysname,@ColName sysname
open T_cursor
fetch next from T_cursor into @tabName,@ColName
while @@fetch_status=0
begin
    exe sp_rename @tabName,@ColName
    fetch next from T_cursor into @tabName,@ColName
end
close T_cursor
deallocate T_cursor