Ok, I gave it a try, and Microsoft.SqlServer.Management.SqlParser.Parser from the Microsoft.SqlServer.SqlManagementObjects nuget package might be the way to go.

var sql = @"SELECT .... ";
var result = Parser.Parse(sql);
var batch = result.Script.Batches.First();
var select = batch.Children.Cast<SqlSelectStatement>().First();
var selectSpec = select.SelectSpecification;

As a result of this, selectSpec.Xml contains a hierarchical XML document representing the SELECT statement.

The SqlCodeObject s provide an Accept() method that implement the Visitor pattern. Write a visitor and evaluate expressions of the types you are interested in: SqlNullScalarExpression , SqlUserDefinedScalarFunctionCallExpression , maybe more.

Be aware that the parser does not recognize user-defined functions without the dbo. schema:

<SqlNullScalarExpression Location="((3,9),(3,137))">
              <!--COALESCE(UPPER(FUNCTION_TO_FIND(B.PARAM, B.TEST_PARAM1, B.TEST_PARAM2, B.TEST_PARAM3,'Routine Type', ATT_TO_DATE())),'NO_VALUE')-->
            </SqlNullScalarExpression>

versus

<SqlUserDefinedScalarFunctionCallExpression Location="((4,15),(4,122))" ObjectIdentifier="DBO.FUNCTION_TO_FIND">
              <!--DBO.FUNCTION_TO_FIND( B.PARAM , B.TEST_PARAM1 , B.TEST_PARAM2, B.TEST_PARAM3,'Routine Type', ATT_TO_DATE())-->
              <SqlObjectIdentifier Location="((4,15),(4,35))" SchemaName="DBO" ObjectName="FUNCTION_TO_FIND">
                <!--DBO.FUNCTION_TO_FIND-->
                <SqlIdentifier Location="((4,15),(4,18))" Value="DBO">
                  <!--DBO-->
                </SqlIdentifier>
                <SqlIdentifier Location="((4,19),(4,35))" Value="FUNCTION_TO_FIND">
                  <!--FUNCTION_TO_FIND-->
                </SqlIdentifier>
              </SqlObjectIdentifier>
              ...
              <SqlBuiltinScalarFunctionCallExpression Location="((4,108),(4,121))" FunctionName="ATT_TO_DATE" IsStar="False">
                <!--ATT_TO_DATE()-->
              </SqlBuiltinScalarFunctionCallExpression>
            </SqlUserDefinedScalarFunctionCallExpression>

I used the following in c# mixed with regex to accomplish this task, yes it's very rough and could use some refinement but it does work. Feel free to improved the answer however I will not be able to provide ongoing support for the code below.

Console.WriteLine("=====================================================");
        int numberOfIterations = 0;
        do
        {// Variables
            string searchResult; // Start of function call
            string functionName; // Function Name
                                 // funcFonud will be char length of function ( "IN (" length is 4)
                                 // used to trim the substring for the next iteration.
            int funcFoundNameLen = 0;
            int funcFoundAt = 0;
            int matchedParenEnd = 0; // End of Matched Paren

            // Find the start of a function call.
            string findFuncReg = "[a-zA-Z][^)( \\r\\n\\\"]+?\\s*?\\("; // fixes [[:alnum:]] issue
            if (query.Length > 0)
            {
                Match match = Regex.Match(query, findFuncReg);
                Console.WriteLine("Trying to match: "+query);
                if (match.Success && match.Index >= 0) // Function call found
                {
                    try
                    {
                        Console.WriteLine("Match was found: " + match.Index.ToString() + match.Value);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                    funcFoundNameLen = match.Length;
                    funcFoundAt = match.Index;
                    searchResult = query.Substring(funcFoundAt);

                    functionName = searchResult.Substring(0, searchResult.IndexOf('(')).Trim();
                    writeFunc(functionName);
                }
                else
                {
                    Console.WriteLine("Function Start NOT found");
                    return;
                }

                string subString = query.Substring(match.Index);
                int openParen = 0;

                // Following finds the matched paren
                for (int i = 0; i < subString.Length; i++)
                {
                    if (subString[i] == ')' && openParen == 1)
                    {
                        matchedParenEnd = i + 1; // Location of the end Paren
                        break;
                    }
                    // Following handles opening and closing of paren so that we
                    // can find the matched paren.
                    else
                    {
                        if (subString[i] == '(')
                        {
                            openParen = openParen + 1;
                        }
                        if (subString[i] == ')')
                        {
                            openParen = openParen - 1;
                        }
                    }
                }

                // Output function call.
                string subCall = subString.Substring(funcFoundNameLen, matchedParenEnd- funcFoundNameLen);
                Console.WriteLine("Sub Call: " + subCall);

                // Set up for recursive call.
                // string querySub = query.Substring(funcFoundAt);
                // Console.WriteLine("querySub: " + querySub);
                matchedParen(subCall);

                // Substring based on the location that the function call was
                // made to the end of the function call
                Console.WriteLine("Remove: " + (funcFoundAt + matchedParenEnd).ToString() + " letters");
                query = query.Substring(funcFoundAt + matchedParenEnd);
                numberOfIterations++;
            }
        } while (query.Length > 0 
        // && numberOfIterations < 1
        );