刪除漢字:
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 ='031851001845'
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 '031851001845' as ORG, dbo.ufn_convertWideNumericToAnsi('031851001845') DES
ORG DES
------------------------ ---------------------------
031851001845 031851001845
