Regular expression to match optional tags (sqaure brackets)… (find & replace in SQL queries, Notepad++)

时间:2023-01-17 23:36:47

I am trying to construct a regular expression that will find and replace occurrences of certain SQL schema qualifications in over 100 SQL files.

我正在尝试构建一个正则表达式,它将在100多个SQL文件中查找和替换某些SQL模式资格的出现。

The original files can contain schema qualifications that may(optionally) have square brackets ('[' and ']') or not. For example a script file may contain:

原始文件可以包含可以(可选)具有方括号('['和']')或不具有方括号的模式资格。例如,脚本文件可能包含:

[database].[dbo].[table_name]

or

要么

database.[dbo].[table_name]

or

要么

database.dbo.[table_name]

and all possible combinations...

和所有可能的组合......

I wrote something like this:

我写了这样的话:

([)?database(])?\.([)?dbo(]?)\.([?)table_name(]?)

Not really working (regex101)

不是真的有效(regex101)

Update (with solution based on answer):

更新(基于答案的解决方案):

Expansion based on @Toto's selected answer below, with addition of a possible table alias to match following string and provision of space prefixes and suffixes

扩展基于@Toto在下面选择的答案,添加了一个可能的表别名来匹配后面的字符串并提供空格前缀和后缀

database.dbo.table_name  tn 
(\s+)(\[)?database(\])?\.(\[)?dbo(\]?)\.(\[?)table_name(\]?)((\s+)(tn))?(\s+)?

https://regex101.com/r/Rz9MLB/7

https://regex101.com/r/Rz9MLB/7

1 个解决方案

#1


1  

You have to escape the square brackets as they are special in regex.

你必须逃避方括号,因为它们在正则表达式中是特殊的。

And the groups are superfluous, your regex becomes:

这些团体是多余的,你的正则表达式变成:

\[?database\]?\.\[?dbo\]?\.\[?table_name\]?

#1


1  

You have to escape the square brackets as they are special in regex.

你必须逃避方括号,因为它们在正则表达式中是特殊的。

And the groups are superfluous, your regex becomes:

这些团体是多余的,你的正则表达式变成:

\[?database\]?\.\[?dbo\]?\.\[?table_name\]?