Hello folks,

Does anyone have a regular expression that does the equivalent of the infamous function, SQL_Signature.

 
thanks
 

Here is a CLR UDF I prepared for "Inside SQL Server 2005: Query Tuning and Optimization" (which I modified from the version presented in "Inside SQL Server 2005: T-SQL Querying" to better support NULLs):

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true)]
public static SqlString sqlsig(SqlString querystring)
{
    return (SqlString)Regex.Replace(
       querystring.Value,
       @”([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?:(?# expression coming
       )(?:([N])?(')(?:[^']|'')*('))(?# character
       )|(?:0x[\da-fA-F]*)(?# binary
       )|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?# precise number
       )(?:[eE]?[\d]*)))(?# imprecise number
       )|(?:[~]?[-+]?(?:[\d]+))(?# integer
       )|(?:[nN][uU][lL][lL])(?# null
       ))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?# operators
       )))”,
       @”$1$2$3#$4”);
}

Thanks Adam.

Is there a way to keep the query inside sp_executesql. For example,

exec sp_executesql N'select customer_id from customer where zip=@zipcode', @zipcode='19104' 

returns as

exec sp_executesql N'#', @zipcode='#'

thanks 

Is there a way?  Sure.  With this particular RegEx?  No :-) ... and I'm not at all good enough with RegEx to tell you how to do it.  I'm not sure what you're working on here, but have you played around with ClearTrace ( http://www.cleardata.biz/cleartrace/ )?  It's a free tool that does this work against trace files, and it works quite well with sp_execute, sp_executesql, sp_cursorprep, and similar.

Thanks Adam. I was at TechEd and Andrew Kelly in one of his sessions waved his regex magic wand to make this happen. I wasnt sure if it was that simple :)

This tool is pretty sweet. Ofcourse less control with singleton sorting but atleast gets us past the signature snag.

OK I didn't realize this at the time but it apparently doesn't work the same way with sp_executesql if the final parameter is defined as an INT vs. a VARCHAR.  The following works as expected in that it leaves the dynamic sql text itself intact. But if I use a VARCHAR it behaives as shown in the post above. I suppose it is a regex thing and as Adam stated I am not that much of a RegEx expert to determine why.

SELECT dbo.fn_RegexReplace('EXEC sp_executesql N''SELECT * FROM [dbo].[OrderHeader] WHERE CustomerID = @x'',N''@x int'',@x = 12350 ',

N'([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?# expression coming

))', N'$1$2$3#$4') AS [Text]

 Andy

thank you very much for responding back.

Great sessions last week, was worth the standing. btw, the SQL trace session scripts never got posted.