USE [litigation] GO Object UserDefinedFunction [dbo].[f_Convert] Script Date 02172014 114204 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[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 @i0 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 USE [litigation] GO /****** Object: UserDefinedFunction [dbo].[f_replace] Script Date: 02/17/2014 11:41:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[f_replace]( @str NVARCHAR(4000) --要转换的字符串 )RETURNS nvarchar(4000) AS BEGIN --过滤0 --如果存在第号这种字符串则进行操作 if @str like '%第0%号%' begin select @str=LEFT(@str,patindex('%第0%号%',@str))+ substring(@str,patindex('%第0%号%',@str)-1+ patINDEX('%[1-9]%',substring(@str,patindex('%第0%号%',@str),LEN(@str))), LEN(@str)) end --调用全角半角转化函数 select @str=dbo.f_convert(@str,0) --替换忽略词(此处本该可以调用正则替换函数过滤忽略词,但是由于权限不够sp_OACreate 'VBScript.RegExp') select @str=replace(@str,'号','') select @str=replace(@str,'第','') select @str=replace(@str,'字','') select @str=replace(@str,'(','') select @str=replace(@str,')','') select @str=replace(@str,'<','') select @str=replace(@str,'>','') select @str=replace(@str,'/','') select @str=replace(@str,',','') select @str=replace(@str,'{','') select @str=replace(@str,'}','') select @str=replace(@str,' ','') select @str=replace(@str,' ','') RETURN(@str) END
USE [litigation]
GO
Object UserDefinedFunction [dbo].[f_Convert] Script Date 02172014 114204
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[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 @i0
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
USE [litigation]
GO
/****** Object: UserDefinedFunction [dbo].[f_replace] Script Date: 02/17/2014 11:41:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[f_replace](
@str NVARCHAR(4000) --要转换的字符串
)RETURNS nvarchar(4000)
AS
BEGIN
--过滤0
--如果存在第号这种字符串则进行操作
if @str like '%第0%号%'
begin
select @str=LEFT(@str,patindex('%第0%号%',@str))+
substring(@str,patindex('%第0%号%',@str)-1+
patINDEX('%[1-9]%',substring(@str,patindex('%第0%号%',@str),LEN(@str))),
LEN(@str))
end
--调用全角半角转化函数
select @str=dbo.f_convert(@str,0)
--替换忽略词(此处本该可以调用正则替换函数过滤忽略词,但是由于权限不够sp_OACreate 'VBScript.RegExp')
select @str=replace(@str,'号','')
select @str=replace(@str,'第','')
select @str=replace(@str,'字','')
select @str=replace(@str,'(','')
select @str=replace(@str,')','')
select @str=replace(@str,'<','')
select @str=replace(@str,'>','')
select @str=replace(@str,'/','')
select @str=replace(@str,',','')
select @str=replace(@str,'{','')
select @str=replace(@str,'}','')
select @str=replace(@str,' ','')
select @str=replace(@str,' ','')
RETURN(@str)
END