替换sql中的字符串
我的表中有 500 条记录。一列保存所有 html 数据。所以例如- html>身体> ... /正文> /html>. 我想做的是查找和替换。我在每条记录中都有这个标签 - 我想用
替换所有行中的内容 现在困难的部分是所有“某事某事”对于每个替代品都是不同的。 所以唯一常见的标签是“table id=”。如何在这里找到替代品?
i have 500 records in a table. one column holds all html data. so e.g -
html> body> ... /body> /html>.
What i want to do is a find and replace. i have this tag in every single record -<table id="something something" />
i want to replace that in all the rows with <table id="" />
now the difficult part is all the "something something" is different for each and every alt.
So the only common tag is "table id=". how can do a find an replace here?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
使用REPLACE函数:
SQL Server 2005+ 具有 CLR 功能,您需要使用该功能来创建正则表达式替换函数,以便更适合单个查询。请参阅此页面获取可下载脚本和源代码。
对于之前的 SQL Server 版本,您最好将内容保存到文本文件中并保存到文本文件中。通过正则表达式/等更新内容,以覆盖现有内容。
Use the REPLACE function:
SQL Server 2005+ has CLR functionality, which you'd need to use to create a regex replace function in order to be more accommodating as a single query. See this page for both a downloadable script, and the source code.
For SQL Server versions prior to that, you might just be better off getting the content to a text file & updating the content via regex/etc, for overwriting the existing content.
尝试一下(假设每个表中仅出现一个“表 ID”):
编辑:根据下面评论中的反馈,此修改后的代码应该可以工作。
Try this (assumes only one "table id" occurs in each):
EDIT: Based on feedback in the comments below, this modified code should work.
如果您编写一个游标函数来遍历表的每一行(非常低效,但我猜您只执行一次?)
然后对该字符串进行替换:
我认为这应该满足您的需要,如果您需要的话,这里是基本的光标功能:
http://blog.sqlauthority.com /2007/01/01/sql-server-simple-example-of-cursor/
编辑:
实际上,我测试了更多,但我无法让它接受通配符,尽管它没有抱怨,但我看不到它正常运行......
If you write a cursor function to go through each row of the table (very inefficient, but I'm guessing that you're only doing this once?)
Then do a replace on that string:
I think that should do what you need, and here is basic cursor functionality if you need it:
http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/
EDIT:
Actually, I tested a bit more, and I can't get it to accept the wildcard, although it doesn't complain, I don't see it functioning properly...
您可能需要编写一个存储过程来执行此操作,其中包含要查找的 SELECT 语句和要替换的 UPDATE 语句。
You will likely need to write a stored procedure to do this, a combination of a SELECT statement to find and an UPDATE statement to replace.