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

-- 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