常用sql数据处理函数

本文关键字: sqlserver

转自网络。

删除汉字：

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

SQL去除字符串HTML标签函数：

CREATE FUNCTION [dbo].[StripAllTags]
(
@input NVARCHAR(MAX)–2000内改为VARCHAR(8000)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
declare
@Result nvarchar(MAX),
@start int,
@end int,
@len int

set @input = @input+’<>‘
set @Result = ”
set @len=len(@input)
set @start = charindex(‘<',@input,1) set @end = charindex('>‘,@input,@start)
while(@start<@end) begin if(@start<>1)
set @Result = @Result + substring(@input,1,@start-1)
set @len = @len – @end
set @input = substring(@input,@end+1,@len)
set @start = charindex(‘<',@input,1) set @end = charindex('>‘,@input,@start)
end

RETURN replace(@Result,’ ‘,”)
END