{"id":2117,"date":"2012-05-09T04:03:15","date_gmt":"2012-05-09T04:03:15","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2117"},"modified":"2012-05-09T04:03:15","modified_gmt":"2012-05-09T04:03:15","slug":"sql%e8%af%ad%e5%8f%a5%e5%8f%82%e6%95%b0%e5%8c%96","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/05\/09\/sql%e8%af%ad%e5%8f%a5%e5%8f%82%e6%95%b0%e5%8c%96\/","title":{"rendered":"sql\u8bed\u53e5\u53c2\u6570\u5316"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\n\/*\r\n\u7528\u9014\uff1a\u5c06sql\u8bed\u53e5\u4f20\u6765\u7684\u53c2\u6570#\u5316\uff0c\u4ee5\u5f97\u5230\u6807\u51c6\u7684sql\u8bed\u53e5\r\n\u4f7f\u7528\u4e3e\u4f8b\uff1aSELECT dbo.SQLSig(&#39;select * from t1 where t1.name=2&#39;,1000)\r\n*\/\r\n\r\nIF OBJECT_ID(&#39;dbo.SQLSig&#39;, &#39;FN&#39;) IS NOT NULL\r\n  DROP FUNCTION dbo.SQLSig;\r\nGO\r\n\r\nCREATE FUNCTION dbo.SQLSig \r\n  (@p1 NTEXT, @parselength INT = 4000)\r\nRETURNS NVARCHAR(4000)\r\n\r\n-- Strips query strings\r\nAS\r\nBEGIN \r\n  DECLARE @pos AS INT;\r\n  DECLARE @mode AS CHAR(10);\r\n  DECLARE @maxlength AS INT;\r\n  DECLARE @p2 AS NCHAR(4000);\r\n  DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);\r\n  DECLARE @p2len AS INT;\r\n\r\n  SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));\r\n  SET @maxlength = CASE WHEN @maxlength &gt; @parselength \r\n                     THEN @parselength ELSE @maxlength END;\r\n  SET @pos = 1;\r\n  SET @p2 = &#39;&#39;;\r\n  SET @p2len = 0;\r\n  SET @currchar = &#39;&#39;;\r\n  set @nextchar = &#39;&#39;;\r\n  SET @mode = &#39;command&#39;;\r\n\r\n  WHILE (@pos &lt;= @maxlength)\r\n  BEGIN\r\n    SET @currchar = SUBSTRING(@p1,@pos,1);\r\n    SET @nextchar = SUBSTRING(@p1,@pos+1,1);\r\n    IF @mode = &#39;command&#39;\r\n    BEGIN\r\n      SET @p2 = LEFT(@p2,@p2len) + @currchar;\r\n      SET @p2len = @p2len + 1 ;\r\n      IF @currchar IN (&#39;,&#39;,&#39;(&#39;,&#39; &#39;,&#39;=&#39;,&#39;&lt;&#39;,&#39;&gt;&#39;,&#39;!&#39;)\r\n        AND @nextchar BETWEEN &#39;0&#39; AND &#39;9&#39;\r\n      BEGIN\r\n        SET @mode = &#39;number&#39;;\r\n        SET @p2 = LEFT(@p2,@p2len) + &#39;#&#39;;\r\n        SET @p2len = @p2len + 1;\r\n      END \r\n      IF @currchar = &#39;&#39;&#39;&#39;\r\n      BEGIN\r\n        SET @mode = &#39;literal&#39;;\r\n        SET @p2 = LEFT(@p2,@p2len) + &#39;#&#39;&#39;&#39;;\r\n        SET @p2len = @p2len + 2;\r\n      END\r\n    END\r\n    ELSE IF @mode = &#39;number&#39; AND @nextchar IN (&#39;,&#39;,&#39;)&#39;,&#39; &#39;,&#39;=&#39;,&#39;&lt;&#39;,&#39;&gt;&#39;,&#39;!&#39;)\r\n      SET @mode= &#39;command&#39;;\r\n    ELSE IF @mode = &#39;literal&#39; AND @currchar = &#39;&#39;&#39;&#39;\r\n      SET @mode= &#39;command&#39;;\r\n\r\n    SET @pos = @pos + 1;\r\n  END\r\n  RETURN @p2;\r\nEND\r\nGO\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\/* \u7528\u9014\uff1a\u5c06sql\u8bed\u53e5\u4f20\u6765\u7684\u53c2\u6570#\u5316\uff0c\u4ee5\u5f97\u5230\u6807\u51c6\u7684sql\u8bed\u53e5 \u4f7f\u7528\u4e3e\u4f8b\uff1aSELECT dbo.SQLSig( [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2117","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2117","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2117"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2117\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}