Regex match last square brackets in SQL Script SSMS

Question

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:

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.

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?

Perhaps use 2 capturing groups with a replacement of $1'$2' for example (AS )\[([^\]]+)\] See regex101.com/r/8fmh11/1 — The fourth bird
– The fourth bird, Commented Oct 19, 2019 at 15:52

The fourth bird · Accepted Answer · 2019-10-19 15:55:52Z

2

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'

answered Oct 19, 2019 at 15:55

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regex match last square brackets in SQL Script SSMS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related