34,591
社区成员
发帖
与我相关
我的任务
分享
这是原来的数据
create table tble(title varchar(10),con text)
insert into tble
select 'aa',
'<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5">
<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=20070529090229560yx5064">
<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=20070529090229560yx5061">
<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=20070529090229023yx5062#51">
<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=20070529090229010yx5065#6">
<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=20070529090222450yx504x#5xer">
'
要的结果是
<A href="2007052909022930yx5064.html?#5">
<A href="20070529090229560yx5064.html?">
<A href="20070529090229560yx5061.html?">
<A href="20070529090229023yx5062.html?#51">
<A href="20070529090229010yx5065.html?#6">
<A href="20070529090222450yx504x.html?#5xer">
要考滤到的问题
1.这个表中的con字段是text类型的,里面的数据绝对大于8000个字符
2.原表中没有.html?这几个字符
3.最好用一个自定义函数解决这个问题
4.原数据和结果都是一条记录
select '<A href="'+SUBSTRING (aa,
CHARINDEX('=',aa,10)+1,
CHARINDEX('#',aa,10)
-CHARINDEX('=',aa,10)-1
)+'.html?'
+SUBSTRING (aa,
CHARINDEX('#',aa,10),
len(aa)
-CHARINDEX('#',aa)+1
)
+'>"'
select '<A href="'+SUBSTRING ('<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',
CHARINDEX('=','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',10)+1,
CHARINDEX('#','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',10)
-CHARINDEX('=','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',10)-1
)+'.html?'
+SUBSTRING ('<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',
CHARINDEX('#','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',10),
len('<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5')
-CHARINDEX('#','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5')+1
)
+'"'
select ''+SUBSTRING ('<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',
CHARINDEX('=','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',10)+1,
CHARINDEX('#','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',10)
-CHARINDEX('=','<A href="http://localhost:2149/Web/help/help_view.aspx?FunctionModuleid=2007052909022930yx5064#5',10)-1
)