SQL Server 正则表达式 无法作用于SQL_MODULES 中的Definition字段

十林 2014-03-10 03:08:10
本人写一个函数，来使用正则表达式替换SQL_MODULES 中的Definition字段中的注释。
貌似definition中的回车换行无法再下面的第二段代码中被识别，麻烦大伙看看。
我使用以下代码测试，可以成功替换。
DECLARE @vSP_Definition VARCHAR(max)

SET @vSP_Definition='--wert '+CHAR(10)+'sdfs/**********************'+CHAR(10)+'*************erer**/ --ewrRTRewr '+CHAR(10)+'--EDFSDF '

+CHAR(10)+'--EREWR '





PRINT @vSP_Definition

PRINT CHAR(10)+'----------------RESULT--------------------------'

SET @vSP_Definition=global.[gf_sys_REG_REPLACE] (@vSP_Definition,N'(\/\*((?!\B(\/\*|\*\/)\B)[\s\S])*?\*\/)',CHAR(10),0) +CHAR(10)



--PRINT @vSP_Definition

print global.[gf_sys_REG_REPLACE] (@vSP_Definition,'--.*'+CHAR(10),N'',0) +CHAR(10)

结果如下
--wert
sdfs/**********************
*************erer**/ --ewrRTRewr
--EDFSDF
--EREWR

----------------RESULT--------------------------
sdfs

但是我如果直接将SQL_MODULES 中的Definition字段的值赋予 @vSP_Definition，进行正则表达式替换就会失败。
DECLARE @vSP_Definition VARCHAR(max)



 SELECT 

         --replace(DEFINITION, '''', '''''') AS SP_Definition,

         @vSP_Definition= DEFINITION

  FROM   SYS.SQL_MODULES SM

  JOIN   SYS.OBJECTS SO

    ON SO.OBJECT_ID = SM.OBJECT_ID

  WHERE  SO.TYPE = 'P' AND OBJECT_NAME(SM.OBJECT_ID) LIKE 'gp_sec_mvp_Values'

--PRINT @vSP_Definition

PRINT CHAR(10)+'----------------RESULT--------------------------'

SET @vSP_Definition=global.[gf_sys_REG_REPLACE] (@vSP_Definition,N'(\/\*((?!\B(\/\*|\*\/)\B)[\s\S])*?\*\/)',CHAR(10),0) +CHAR(10)



PRINT @vSP_Definition --这里正常应该要有输出，但是没有。

print global.[gf_sys_REG_REPLACE] (@vSP_Definition,'--.*'+CHAR(10),N'',0) +CHAR(10)

在我的数据库中我有定义gp_sec_mvp_Values。 使用上面的代码测试，完全没有输出。
global.[gf_sys_REG_REPLACE] 函数定义如下

/****** Object:  UserDefinedFunction [global].[gf_sys_REG_REPLACE]    Script Date: 03/10/2014 15:05:21 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





CREATE FUNCTION [global].[gf_sys_REG_REPLACE] (@SourceStr  NVARCHAR(MAX),--

                                              @pattern    NVARCHAR(255),--pattern  

                                              @replacestr NVARCHAR(255),--replaced by

                                              @IgnoreCase INT = 0 --Character Case,default ingore

)

RETURNS NVARCHAR(MAx) --

AS

  BEGIN

      --Select global.[gf_sys_REG_REPLACE] (N'sadfaf/*rfere8*/',N'sa',N'',0)   

      /* Function Name : [gf_sys_REG_REPLACE]

      * 

      * Description: user Regular Expression to Replace the text

      *

      * Parameters:	  

      *			   

      * Return values: 

      *

      * Examples: 

      *				

       *				

      * Revision history:

     

      */

    DECLARE @hr integer

	DECLARE @objRegExp integer

	DECLARE @result varchar(5000)





	EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT

	IF @hr <> 0 BEGIN

	EXEC @hr = sp_OADestroy @objRegExp

	RETURN NULL

	END

	EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @pattern

	IF @hr <> 0 BEGIN

	EXEC @hr = sp_OADestroy @objRegExp

	RETURN NULL

	END

	EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', 1

	IF @hr <> 0 BEGIN

	EXEC @hr = sp_OADestroy @objRegExp

	RETURN NULL

	END

	

	EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase

	IF @hr <> 0 BEGIN

	EXEC @hr = sp_OADestroy @objRegExp

	RETURN NULL

	END	

	EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @SourceStr, @replacestr

	IF @hr <> 0 BEGIN

	EXEC @hr = sp_OADestroy @objRegExp

	RETURN NULL

	END

	EXECUTE @hr = sp_OADestroy @objRegExp

	IF @hr <> 0 BEGIN

	RETURN NULL

	END







      RETURN @result

  END 



GO


谢谢各位牛人帮助先啦
...全文
230 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
十林 2014-03-17
  • 打赏
  • 举报
 回复
引用 19 楼 fredrickhu 的回复:
[quote=引用 18 楼 linxtong 的回复:] 目前发现的原因是，sp_OAMethod 的output 只支持长度小于8000的字符串。
难道需要像2000的 用几个串来拼接？[/quote] 因为它是output 所以拼不来。。。
--小F-- 2014-03-13
  • 打赏
  • 举报
 回复
引用 18 楼 linxtong 的回复:
目前发现的原因是，sp_OAMethod 的output 只支持长度小于8000的字符串。
难道需要像2000的 用几个串来拼接？
十林 2014-03-13
  • 打赏
  • 举报
 回复
目前发现的原因是，sp_OAMethod 的output 只支持长度小于8000的字符串。
十林 2014-03-11
  • 打赏
  • 举报
 回复
引用 14 楼 rockyljt 的回复:

--请问楼主单查询这个有返回值吗？
SELECT DEFINITION
  FROM   SYS.SQL_MODULES SM
  JOIN   SYS.OBJECTS SO
    ON SO.OBJECT_ID = SM.OBJECT_ID
  WHERE  SO.TYPE = 'P' AND OBJECT_NAME(SM.OBJECT_ID) LIKE '%gp_sec_mvp_Values%'
谢谢，我题目中提到，我已经定义了这个存储过程了，而且上面我也发现是函数中的长度问题。 您的代码有输出，是因为你系统中定义的存储过程代码长度都太短了，你试试找个代码长度超过8000的试试。
直面人生 2014-03-11
  • 打赏
  • 举报
 回复
太吓人了!
---涛声依旧--- 2014-03-10
  • 打赏
  • 举报
 回复 

DECLARE @vSP_Definition VARCHAR(max)

 SELECT 
         --replace(DEFINITION, '''', '''''') AS SP_Definition,
         @vSP_Definition= DEFINITION
  FROM   SYS.SQL_MODULES SM
  JOIN   SYS.OBJECTS SO
    ON SO.OBJECT_ID = SM.OBJECT_ID
  WHERE  SO.TYPE = 'P' 
--应该是楼主下的条件有问题了，我注释掉了就有结果出现了
--AND OBJECT_NAME(SM.OBJECT_ID) LIKE '%gp_sec_mvp_Values%'
--PRINT @vSP_Definition
PRINT CHAR(10)+'----------------RESULT--------------------------'
SET @vSP_Definition=dbo.[gf_sys_REG_REPLACE] (@vSP_Definition,N'(\/\*((?!\B(\/\*|\*\/)\B)[\s\S])*?\*\/)',CHAR(10),0) +CHAR(10)

PRINT @vSP_Definition 

--应该是楼主下的条件有问题了，我注释掉了就有结果出现了
/*
----------------RESULT--------------------------
CREATE PROCEDURE Pr_MoveFile
(
	@DirID int,
	@ParentID int
) 	
AS

DECLARE @Index int
SET @Index = (SELECT ParentID FROM Directory WHERE DirID = @DirID)

UPDATE Directory SET FileCount = FileCount - 1 WHERE DirID = @Index
UPDATE Directory SET FileCount = FileCount + 1 WHERE DirID = @ParentID

UPDATE Directory SET ParentID = @ParentID WHERE DirID = @DirID
*/
---涛声依旧--- 2014-03-10
  • 打赏
  • 举报
 回复 

--请问楼主单查询这个有返回值吗？
SELECT DEFINITION
  FROM   SYS.SQL_MODULES SM
  JOIN   SYS.OBJECTS SO
    ON SO.OBJECT_ID = SM.OBJECT_ID
  WHERE  SO.TYPE = 'P' AND OBJECT_NAME(SM.OBJECT_ID) LIKE '%gp_sec_mvp_Values%'
發糞塗牆 2014-03-10
  • 打赏
  • 举报
 回复
这个不好弄，还是看看能不能用CLR来搞，或者导出到开发环境用vs的自带功能来批量处理
發糞塗牆 2014-03-10
  • 打赏
  • 举报
 回复
如果不用max，nvarchar只能用4000了
十林 2014-03-10
  • 打赏
  • 举报
 回复
引用 10 楼 DBA_Huangzj 的回复:
Select global.[gf_sys_REG_REPLACE] (N'sadfaf/*rfere8*/',N'sa',N'',0) 你这种用法要用nvarchar，不能用varchar
不好意思 这里忘记改了，但是也可以执行的，我试过DECLARE @result nvarchar(max) 也不行 我把函数中的nvarchar 全改成varchar也不行。。 我改成DECLARE @result nvarchar(2000) 或者DECLARE @result varchar(2500) 上面的测试语句就可以出结果了，
發糞塗牆 2014-03-10
  • 打赏
  • 举报
 回复
Select global.[gf_sys_REG_REPLACE] (N'sadfaf/*rfere8*/',N'sa',N'',0) 你这种用法要用nvarchar，不能用varchar
十林 2014-03-10
  • 打赏
  • 举报
 回复
引用 6 楼 DBA_Huangzj 的回复:
varchar(max)可行不？
不可以，我把函数中的DECLARE @result varchar(5000) 改成 @result varchar(max) 以下的表达式输出就NULL了。 Select global.[gf_sys_REG_REPLACE] (N'sadfaf/*rfere8*/',N'sa',N'',0) 不好截取 这个代码是为了去除，存储过程中的注释代码，因为截取会导致原有的代码注释 结构乱了，那么最后结果就不会正确了， 当然预先找到相应的截取点，再截取是可行的，不过那样效率就低了。 谢谢啦 我再研究研究
LongRui888 2014-03-10
  • 打赏
  • 举报
 回复
引用 5 楼 linxtong 的回复:
谢谢楼上的回复。 目前发现的原因，好像是VBScript.RegExp的replace 函数无法对大文本进行处理。 我们这边的存储过程的definition都很长，都会超过8000,（请勿吐槽，这些存储过程不是给OLTP系统使用 ：））。 大伙有遇到过吗？
我觉得还不如这样，你先把所有的存储过程代码，通过“导出脚本”功能导出来，然后自己写个vb程序，打开这个文件，把所有注释替换掉，就行了。
LongRui888 2014-03-10
  • 打赏
  • 举报
 回复
引用 5 楼 linxtong 的回复:
谢谢楼上的回复。 目前发现的原因，好像是VBScript.RegExp的replace 函数无法对大文本进行处理。 我们这边的存储过程的definition都很长，都会超过8000,（请勿吐槽，这些存储过程不是给OLTP系统使用 ：））。 大伙有遇到过吗？
如果太长，能不能，先取一部分，替换，然后再取下一部分替换，最后把结果拼接借来
發糞塗牆 2014-03-10
  • 打赏
  • 举报
 回复
varchar(max)可行不？
十林 2014-03-10
  • 打赏
  • 举报
 回复
谢谢楼上的回复。 目前发现的原因，好像是VBScript.RegExp的replace 函数无法对大文本进行处理。 我们这边的存储过程的definition都很长，都会超过8000,（请勿吐槽，这些存储过程不是给OLTP系统使用 ：））。 大伙有遇到过吗？
--小F-- 2014-03-10
  • 打赏
  • 举报
 回复
吓死人了。。。
發糞塗牆 2014-03-10
  • 打赏
  • 举报
 回复
按我理解数据库本身不是用来处理这些复杂的逻辑，如果你能用CLR实现，我建议不要用纯T-SQL，不过能写出这复杂的T-SQL你也算高手了。跟项目组商量一下吧
十林 2014-03-10
  • 打赏
  • 举报
 回复
引用 1 楼 DBA_Huangzj 的回复:
太高端了，我觉得用CLR来写可能更好，不过...本人不会写CLR
谢谢版主，恩 CLR是可以解决这个问题的，但是我们没有权限在服务器上做这个设置。
發糞塗牆 2014-03-10
  • 打赏
  • 举报
 回复
太高端了，我觉得用CLR来写可能更好，不过...本人不会写CLR
MySQL 查询与高级查询（多表、嵌套和正则表达式）
MySQL提供了功能强大、灵活的语句来实现这些操作，本章将介绍如何使用 SELECT 语句查询数据表中的一列或多列数据、使用集合函数显示查询结果、连接查询、子查询以及使用正则表达式进行查询等。 -------------------...
【数据库】从mysql迁移到sqlserver
以下为一个自学者的探索过程，内容准确性不予保证 我用的工具是navicat premium 右键mysql的数据库选择数据传输 只能选择表，其他默认灰色不能选择（估计是某些函数或者类型不支持造成的，俗话说不兼容），但mysql里的存储过程也有传输，没成功而已。 在高级选项卡里做进一步设置，确定没干扰的话可以去掉一些勾提升执行效率（如不使用事务）。 右边选择远程数据库后，点
Atitit。sql2016标准化的规划方案 v3 q2a
Atitit。sql2016标准化的规划方案 v3 q2a   1. Sql标准化的历史3 1.1. Sql92标准4 1.2. Sql99标准4 1.3. SQL:2003为例，它包括以下9个部分 5 1.4. Sql2006标准6 1.5. Sql2008标准7 1.6. SQL:2011 7 2. 多媒体大文件数据特点8 3. 现有数据存储（包括但不限于数据库）的问题9 ...
MySQL入门
mysql数据库入门知识：结构化查询语言SQL；用户管理与权限管理；数据定义语言（DDL）；单表查询语句（Select）
mysql
定义SQL结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言语法SQL语句可以单行或多行书写，以分号结尾关键字、函数名、列名和索引名不区分大小写数据库的名字、表名、别名区分大小写注释单行注释– 注释内容约束是作用于表中字段上的规则，用于限制存储在表中的数据可以在创建表/修改表的时候添加约束功能加快数据检索的速度，提高效率缺点创建和维护索引都需要消耗时间，消耗的时间长短取决于表中数据量的多少会占用磁盘空间。
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章