declare @w varchar(8000)
declare @t varchar(8000)
set @w='对象资源管理详细信息'
set @w=dbo.regexReplace(@w,'[^\u4e00-\u9fa5]','',1,1)
declare @tb table(w varchar(10),f int)
declare @i int
declare @l int
declare @theWord varchar(10)
set @l=2
while @l<5
begin
set @i=1
while @i<len(@w)
begin
set @theWord=substring(@w,@i,@l)
if charindex(' ',@theWord)=0 begin
if exists(select w from @tb where w=@theWord)
begin
update @tb set f=f+1 where w=@theWord
end
else
begin
insert @tb(w,f) select @theWord,1
end end
set @i=@i+1
end
set @l=@l+1
end
select text from dbo.dictionary where text in(select w from @tb where f>len(@w)/100)mssql
解决方案 »
- sqlserver 最让人郁闷的问题,回者有分!
- SQLServer中有没有类似Oracal里面merge into 这样的语句,以实现“数据已经登录,则update,数据没有登陆,则insert”这样的功能?在线等
- 请问简单问题????
- 存储过程中写行件判断!
- 求高手帮忙,两个数据库同步的问题。急!
- 我想把A库a表的记录插到B库b表里,有什么办法?
- 请教一个SQL
- 求助超级高手,这段c的程序在sql里怎么实现函数************************
- 如何使用MSSQL语句删除数据
- 请问大侠,如何在企业管理器中把用word编辑的文档、表格存入text字段,并如实显示出来。
- SQL2000 TRUNCATE 数据还能恢复吗?
- sql定时从一台机器抽取数据到另一台的数据库
GO
/****** 对象: UserDefinedFunction [dbo].[regexReplace] 脚本日期: 01/11/2013 16:51:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[regexReplace]
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)
exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
end
return @result
end 一个正则替换函数
returns table as return
(
select text from dbo.dictionary
where @w like '%'+text+'%'
and (len(@w)-len(replace(@w,text,'')))/len(text) > len(@w)/100
)
goselect * from fn_tb('对象资源管理详细信息')
USE [test]
GO
/****** 对象: UserDefinedFunction [dbo].[gkey] 脚本日期: 01/11/2013 16:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[gkey]
(
@w varchar(1000) )
returnS varchar(1000) AS
begin
declare @t varchar(8000)
declare @s varchar(8000)
declare @k varchar(8000)
set @w=dbo.regexReplace(@w,'[^\u4e00-\u9fa5]','',1,1)declare @tb table(w varchar(10),f int)
declare @i int
declare @l int
declare @theWord varchar(10)
set @l=2
while @l<5
begin
set @i=1
while @i<len(@w)
begin
set @theWord=substring(@w,@i,@l)
if charindex(' ',@theWord)=0 begin
if exists(select w from @tb where w=@theWord)
begin
update @tb set f=f+1 where w=@theWord
end
else
begin
insert @tb(w,f) select @theWord,1
end end
set @i=@i+1
end
set @l=@l+1
end
set @k=''
Declare cur Cursor
for
select text from dbo.dictionary where text in(select w from @tb)
open cur
declare @text sysname
fetch next from cur into @text
while(@@fetch_status=0)
begin
if (@k='')
begin
set @k=@k+@text
end
else
begin
set @k=@k+','+@text
end
fetch next from cur into @text
endclose cur
deallocate curreturn @k
end