全角转化为半角方法

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

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