这是原来的数据
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 ''+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)
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 '<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
)+'>"'
我个人认为,存储一个链接而已,哪里会用到 text 啊,效率低,使用也不方便
<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">
'这些都是在一个字段里面的啊?刚才没注意到,这样的话,还是前台程序用正则表达式来处理比较好,数据库的处理不是万能的