Efficient means for parsing and replacing values in T-SQL

I have a lot of T-SQL queries I need to parse and replace certain values.

A query may look like this

SELECT dbo.udfTest(123,'Bob') as Foo

or alternatively

SELECT dbo.udfTest(123) as Foo

My task is to replace the number value with another given value but as I contemplate just rolling something up using the string class and doing substrings etc I start to run into lots of edge cases like this

SELECT dbo.udfTest ( 123 ) as Foo

or

SELECT [dbo].[udfTest]( 123 ) as Foo

or

SELECT [dbo].[udfTest]( 123 ) as Foo1, dbo.udfTes开发者_Go百科t(123) as Foo2

Throw in any combination of whitespace, casing, brackets, nested parenthesis and you can imagine the number of variations I would have to cover...nasty.

Which brings me to wondering if there is a better way? RegEx may be a play but I figured I would toss it out to get some opinions.

You might be able to use the database features of Visual Studio. See API Reference for Database Features of Visual Studio, especially the Microsoft.Data.Schema.ScriptDom Namespace.

Also, a quick search for "parse mdx query" turned up several interesting hits. I'm pretty sure I once found a tool that could parse MDX queries, then use the parse tree to create a formatted version.

Perhaps the article Getting to the Crown Jewels will help. If nothing else, it may give you a hint about who to ask for help.

Matching multiple possible patterns says RegEx right off the bat. The RegEx will be ugly but it should get you where you want to go pretty easily.

If the end goal is to replace some set of numbers with a new set of numbers (basically a translation), wouldn't it be easier update your UDF to use a lookup table to map the old values to the new ones instead of trying to intercept every method call before it takes place? (Unless your examples have been greatly simplified in that they only show the same function being called each time, but in reality there's many that would have to be updated)