Regex match last square brackets in SQL Script SSMS

user_6241235 0
Viewed 128

I am trying to perform a regex match in SQL Server Management Studio 2017 to then do a replace on part of a SQL script that looks as follows:

,ISNULL([Gt 01 To 02 Weeks SUM 1],0) AS [Gt 01 To 02 Weeks SUM]
,ISNULL([Gt 02 To 03 Weeks SUM 1],0) AS [Gt 02 To 03 Weeks SUM]

I want to match on the last [ and ] e.g. using example line [Gt 02 To 03 Weeks SUM]; so I can the replace [|] with '

I tried 

(?<=\sAS\s).*(\[)Gt|SUM(\])

And this gives:

enter image description here

which is full matches rather than the group matches I wish to replace

I then tried:

(?<=\sAS\s).*?(\[)|(?<=\sAS\s)?(\])

But this matches one too many (i.e. it matches on the penultimate ] as well.

enter image description here

I tried a few other things to no avail.

Behaviour seems to match as per using PCRE PHP on regex 101. Example: here so I added that tag but I am not experienced enough in this domain to know if that is valid.

How do I change this regex please to only match on the last brackets?

pcre ssms regex
1 Answers

You might use 2 capturing groups and match the [ and ] so that they can be replaced with '

(AS )\[([^\]]+)\]
  • (AS ) Capture group 1 Match AS
  • \[ Match [
  • ( Capture group 2
    • [^\]]+ Negated character class, match any char 1+ times except ]
  • ) Close group
  • \] Match ]

Regex demo

In the replacement use the 2 capturing groups

$1'$2'
user_54249880
Related