全角转半角:(S_SHOW) Declare @p nvarchar(200) Declare @idx int Declare @ret varchar(200) set @p=N'1234567。890' SET @ret='' Set @idx=1 WHILE SUBSTRING(@p,@idx,1)<>'' BEGIN SET @ret=@ret+NCHAR(UNICODE(SUBSTRING(@p,@idx,1))-CASE WHEN SUBSTRING(@p,@idx,1)='。' THEN 12244 ELSE 65248 END ) SET @idx=@Idx+1 END SELECT @ret
半角转全角 Declare @p nvarchar(200) Declare @idx int Declare @ret Nvarchar(200) --set @p=N'1234567。890' set @p=N'1234567.890' SET @ret='' Set @idx=1 WHILE SUBSTRING(@p,@idx,1)<>'' BEGIN SET @ret=@ret+NCHAR(UNICODE(SUBSTRING(@p,@idx,1))+CASE WHEN SUBSTRING(@p,@idx,1)='.' THEN 12244 ELSE 65248 END ) SET @idx=@Idx+1 ENDSELECT @ret
declare @a varchar(1000),@b varchar(1000) select @a='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+0123456789.:";''<>?,.{}|[]\' set @b='' while @a<>'' begin set @b=@b+nchar(unicode(left(@a,1))+65248) print @b set @a=substring(@a,2,1000) endselect @bresult: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+0123456789.:";'<>?,.{}|[]\主要是全角UBICODE字符集和半角不是一一对应的,不好转。普通的好办。
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+0123456789.:";''<>?,.{}|[]\' unicode 在33--126之间,对应全角在65281-65374之间所以: declare @a nvarchar(1000),@b nvarchar(1000) select @a=N'123457890' set @b='' while @a<>'' begin set @b=@b+case when unicode(left(@a,1)) between 65281 and 65374 then left(@a,1) else nchar(unicode(left(@a,1))+65248) end set @a=substring(@a,2,1000) endselect @b
上面那个有BUG,改如下: declare @a nvarchar(1000),@b nvarchar(1000) select @a=N'123457890' set @b='' while @a<>'' begin set @b=@b+case when unicode(left(@a,1)) between 33 and 126 then nchar(unicode(left(@a,1))+65248) else left(@a,1) end set @a=substring(@a,2,1000) endselect @b
create function 全半角 (@str Nvarchar(4000),@flag nchar(4)) returns nvarchar(4000) as begin declare @ nvarchar(4000) set @=N'' if @flag=N'全角' while @str<>N'' select @=@+case when unicode(left(@str,1)) between 33 and 126 then nchar(unicode(left(@str,1))+65248) else left(@str,1) end,@str=substring(@str,2,4000) else while @str<>N'' select @=@+case when unicode(left(@str,1)) between 65281 and 65374 then nchar(unicode(left(@str,1))-65248) else left(@str,1) end,@str=substring(@str,2,4000) return @ end go select dbo.全半角(N'12453ga的sd(a4ss57890','全角')select dbo.全半角(N'12453ga的s(da4ss57890','半角')godrop function 全半角
建一个对照表t(因为它们毕竟不是一一对应的)
qj bj
, ,
. 。
! !然后用一条语句,replace()函数不就可以了?
select nchar(65248+unicode('半角字符'))
将常用全角转化为半角:
select nchar(unicode('全角字符') - 65248)
Declare @p nvarchar(200)
Declare @idx int
Declare @ret varchar(200)
set @p=N'1234567。890'
SET @ret=''
Set @idx=1
WHILE SUBSTRING(@p,@idx,1)<>''
BEGIN
SET @ret=@ret+NCHAR(UNICODE(SUBSTRING(@p,@idx,1))-CASE WHEN SUBSTRING(@p,@idx,1)='。' THEN 12244 ELSE 65248 END )
SET @idx=@Idx+1
END
SELECT @ret
Declare @p nvarchar(200)
Declare @idx int
Declare @ret Nvarchar(200) --set @p=N'1234567。890'
set @p=N'1234567.890'
SET @ret=''
Set @idx=1
WHILE SUBSTRING(@p,@idx,1)<>''
BEGIN
SET @ret=@ret+NCHAR(UNICODE(SUBSTRING(@p,@idx,1))+CASE WHEN SUBSTRING(@p,@idx,1)='.' THEN 12244 ELSE 65248 END )
SET @idx=@Idx+1
ENDSELECT @ret
select @a='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+0123456789.:";''<>?,.{}|[]\'
set @b=''
while @a<>''
begin
set @b=@b+nchar(unicode(left(@a,1))+65248)
print @b
set @a=substring(@a,2,1000)
endselect @bresult:
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+0123456789.:";'<>?,.{}|[]\主要是全角UBICODE字符集和半角不是一一对应的,不好转。普通的好办。
unicode 在33--126之间,对应全角在65281-65374之间所以:
declare @a nvarchar(1000),@b nvarchar(1000)
select @a=N'123457890'
set @b=''
while @a<>''
begin
set @b=@b+case when unicode(left(@a,1)) between 65281 and 65374
then left(@a,1)
else nchar(unicode(left(@a,1))+65248)
end
set @a=substring(@a,2,1000)
endselect @b
declare @a nvarchar(1000),@b nvarchar(1000)
select @a=N'123457890'
set @b=''
while @a<>''
begin
set @b=@b+case when unicode(left(@a,1)) between 33 and 126
then nchar(unicode(left(@a,1))+65248)
else left(@a,1)
end
set @a=substring(@a,2,1000)
endselect @b
returns nvarchar(4000)
as
begin
declare @ nvarchar(4000)
set @=N''
if @flag=N'全角'
while @str<>N''
select @=@+case when unicode(left(@str,1)) between 33 and 126 then nchar(unicode(left(@str,1))+65248) else left(@str,1) end,@str=substring(@str,2,4000)
else
while @str<>N''
select @=@+case when unicode(left(@str,1)) between 65281 and 65374 then nchar(unicode(left(@str,1))-65248) else left(@str,1) end,@str=substring(@str,2,4000)
return @
end
go
select dbo.全半角(N'12453ga的sd(a4ss57890','全角')select dbo.全半角(N'12453ga的s(da4ss57890','半角')godrop function 全半角
STUDY :-)