删除数据库字段中的汉字或字符

删除数据库字段中的汉字或字符

----（Function 源于网络）

删除汉字：

CREATE FUNCTION DeleteHZ(@cargoname varchar(512))

RETURNS varchar(512)

AS

BEGIN

DECLARE @Result varchar(512)

declare @sno smallint

select @Result=''

select @sno=1

while(@sno<=datalength(@cargoname))

begin

if datalength(SUBSTRING(@cargoname,@sno, 1))=1

set @Result=@Result+SUBSTRING(@cargoname,@sno, 1)

set @sno=@sno+1

end

RETURN(@Result)

END

select dbo.DeleteHZ(JobPosition) from PRC_PersonalInfo

---Result---

CUSTOMS CUSTOMS 報關員

删除字符：

CREATE FUNCTION DeleteEN (@cargoname varchar(512))
RETURNS varchar(512)
AS
BEGIN
DECLARE @Result varchar(512)
declare @sno smallint
select @Result=''
select @sno=1
while(@sno<=datalength(@cargoname))
begin
if datalength(SUBSTRING(@cargoname,@sno, 1))=2
set @Result=@Result+SUBSTRING(@cargoname,@sno, 1)
set @sno=@sno+1
end
RETURN(@Result)

替换SQL中的全角数字为半角数字

create function Ufn_convertWideNumericToAnsi
(
@vstrIn varchar(1000)
)
returns varchar(4000)
as
begin
declare @strReturn varchar(4000)
,@bin varbinary(4000)
,@str varchar(4000)
,@stmp varchar(4)
,@i int
,@len int
-- ,@vstrIn varchar(1000)

--set @vstrIn =' ０３１８５１００１８４５'
set @strReturn=''
set @bin=convert(varbinary(4000),@vstrIn)
exec master..xp_varbintohexstr @bin, @str out

select @str=stuff(@str,1,2,'')
set @len=len(@str)
set @i=1
while @i<@len
begin
set @stmp = substring(@str,@i,4)
if(substring(@stmp,1,1) <> 'A')
return @vstrIn
set @stmp = replace(@stmp,'A','')
set @stmp = replace(@stmp,'B','')
--print @stmp
set @stmp = cast((convert(int,@stmp)-30) as varchar(1))
set @strReturn = @strReturn + @stmp
set @i=@i+4
end
--print @strReturn
return @strReturn
end

示例

select ' ０３１８５１００１８４５' as ORG, dbo.ufn_convertWideNumericToAnsi('０３１８５１００１８４５') DES

ORG DES
------------------------ ---------------------------
０３１８５１００１８４５ 031851001845