主页 / dba / 问题 / 238063
Accepted
SQLPRODDBA
Asked: 2019-05-14 13:46:19 +0800 CST

在 SQL Server 查询编辑器中需要正则表达式的帮助

  • 772

我有一个包含数千行代码的文件，其索引详细信息如下：

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  
BEGIN  
 CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  
(  
[cd] ASC   
)   
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  
END  


IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  
BEGIN  
 CREATE INDEX [IX_Test2] ON [Test2]  
(  
[AdID] ASC , [Date] ASC   
)  INCLUDE ( [AID], [LM] )   
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  
END

我想像下面这样格式化这些数据以进行比较：

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  BEGIN   CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  (  [cd] ASC   )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  END   
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  BEGIN   CREATE INDEX [IX_Test2] ON [Test2]  (  [AdID] ASC , [Date] ASC   )  INCLUDE ( [AID], [LM] )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  END

所以基本上 IF 和 END 之间的所有内容都应该排成一行。我尝试使用 REGEX 来完成它，但没有得到它的工作。如果有人可以在这方面帮助我，那将是很大的帮助。

如果您需要更多详细信息，请告诉我。

regular-expression regex

1 个回答

  1. Best Answer

    如果您愿意分两步执行此操作，则可以使用 SSMS 替换对话框中的 Regex 选项。

    步骤1

    删除所有行尾，将其全部压缩为一行。

    Replace: \r\n

    With:

    解释：\r\n查找行结尾，几乎可以肯定是回车 (\r) 后跟换行 (\n)。什么都不换。

    第2步

    将所有出现的“END”替换为“END”加上一个新的行尾。

    Replace: END

    With: END\r\n

    说明：将“END”替换为“END”加回车加换行。

    古怪

    您上面的示例在第一个“END”之后有两个空格，第二个之后没有空格。这将导致第 2 步之后的结果如下所示：

    IF NOT EXISTS...
  IF NOT EXISTS...

    如果您的真实代码也有这种奇怪之处，您可以通过确保“END”之后没有空格来解决这个问题，步骤 0 为：

    Replace: END（“END”后两个空格）

    With: END（“END”后没有空格）

    在 SSMS 17.9.1 中测试。

    • 2

相关问题