Regex replace with capturing groups

Question

I wish to refactor code using .NET regular expressions. The aim is to split declarations and assignments (for backwards compatibility with SQL 2005).

Sample Input:

DECLARE @clientCode char(10), @city nvarchar(100) = '', @country char(2) = 'US',
   @clientId int

Desired Output:

DECLARE @clientCode char(10), @city nvarchar(100), @country char(2),
   @clientId int
SELECT @city = '', @country = 'us'

This is what I have so far to match the input:

DECLARE\s+
(
    ,?
    (@\w+\s+)
    (\(.+\))?
    (\=\s+\w+)?
)+

What replacement regex could I use to get the expected output?

Nick Butler · Accepted Answer · 2012-05-03 11:36:13Z

1

Even if you get this working with a RegEx, it will be a maintenance nightmare.

I suggest you write out some well commented string manipulation in code.

answered May 3, 2012 at 11:36

Nick Butler

24.5k4 gold badges51 silver badges72 bronze badges

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

1 Comment

Reluctantly I think you're right. Using regular expressions for this would be maintenance headache.

David Brabant · Accepted Answer · 2012-05-03 14:52:42Z

1

Well, just for the fun of it, if you happen to have SQL Server 2012 feature pack at hand, you might use Microsoft.SqlServer.Management.SqlParser.Parser to parse your existing SQL 20xx code and generate the equivalent 2005 code. You might even do so with PowerShell. Nice weekend project, eh? :-)

answered May 3, 2012 at 14:52

David Brabant

44k16 gold badges93 silver badges119 bronze badges

Great answer! Thanks for pointing me to the Transact-SQL Language Service for parsing TSQL.