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

Question

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

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)

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

database.dbo.table_name  tn 

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

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

Answer 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\]?