对varchat(max)或者 text字段进行操作,估计很多人会遇到8K字符的限制。
网上流传的最多的是用存储过程 把字符取成小段小段的做。很麻烦。
今采用 SQL Server 2005 SQLCLR搞定。
步骤如下:
1.首先安装sqlserver2005和visual studio 2005。启用sqlserver2005的clr ,应先在sqlsever的查询分析器按实际情况执行下边的语句已配置 CLR支持
exec sp_configure 'clr enabled'--查看状态
exec sp_configure 'clr enabled',1--1,启用clr\0,禁用clr
reconfigure with override--按提示(配置选项 'clr enabled' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。)运行此句
2.第一步,打vs新建项目类型-数据库-SQL Server项目。
(这部分可以参考 http://www.chenjiliang.com/Article/View.aspx?ArticleID=2413 SQLCLR(一)入门)
代码如下:
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
//注意 12行 加入这行可以突破 T-SQL 8000字符的限制。
3.可以在sqlsever的查询分析器测试
select dbo.GLRegexReplace('abc','abc','def')
4.发布 用VS2005直接运行就可以了。如果需要发布到其他的电脑
参考
http://nayyeri.net/blog/create-assembly-issue-with-visual-studio-sql-server-database-projects/