I recently had an issue where I had a general search procedure which took a comma delimited list of search terms, and I wanted to data mine the combination of the parameter values called in production, to establish things such as most popular searches and poorly performing search options. Itzik BenGan has already written about grouping proc and query calls, but I needed to group the param names and values. The following regEx string allowed me to extract the parameter names and values as an xml fragment from a profiler trace. This can then be cast to xml in Sql 2005 and shredded into a table using XPath that, if you want, you could mine for free using the table analysis feature in the cloud mining project: http://www.sqlserverdatamining.com/cloud/

 

@(?'Param'[a-z0-9A-Z_]+) = (?:(?:'(?'Value'[^']*?)')|(?'Value'[^']+?))(?:,|$)

 

Many thanks to Baron Von Garfoot (Rob Garfoot -www.garfoot.com/blog) and Commander B Benbrook (aka Rubert Benbrook - http://phazed.com), who, with impish glee, led me through getting to grips with RegEx using http://www.radsoftware.com.au/regexdesigner/

 

If you need a snippet to start using it, this will get you out of the gate until I can write something a little sharper that doesn't rely on string concatenation to build an xml fragment.

 

 private static string spParams(string inputString, string ElementName)

    {

 

        string patternMatch = "@(?'Param'[a-z0-9A-Z_]+) = (?:(?:'(?'Value'[^']*?)')|(?'Value'[^']+?))(?:,|$)";

 

        Match m = Regex.Match(inputString, patternMatch);

 

 

        StringBuilder sb = new StringBuilder(100);

        sb.Append("<");

        sb.Append(ElementName);

 

        while (m.Groups.Count != 1)

        {

            string paramName = m.Groups[1].Value;

            string paramValue = m.Groups[2].Value;

 

            paramValue = paramValue.Replace("&", @"&amp;");

            paramValue = paramValue.Replace("%", @"&#37;");

            paramValue = paramValue.Replace("<", @"&lt;");

            paramValue = paramValue.Replace(">", @"&gt;");

 

            string paramString = string.Format(" {0}=\"{1}\" ", paramName, paramValue);

 

            sb.Append(paramString);

            m = m.NextMatch();

        }

 

        sb.Append("/>");

 

        return sb.ToString();

 

    }