Search code examples
regexssmspcre

Regex match last square brackets in SQL Script SSMS

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?

Solution

  • 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'