SqlServer特殊字元轉換&查詢

  

Codesnip, currently in used in my project:

 

    /// <summary>

/// Encode the user-input (with special character) into SQL query statement

/// special character like : ',[,/,_,%...etc

/// </summary>

/// <param name="strValue"> user-input </param>

/// <param name="isLikeStatement">if it is encode for like statement</param>

/// <returns>SQL query statement</returns>
public static string sqlEncode(string strValue, bool isLikeStatement)

    {

        string rtStr = strValue;

        if (isLikeStatement)

        {

            rtStr = strValue.Replace("[", "[[]"); //此句一定要在最前

            rtStr = rtStr.Replace("_", "[_]");

            rtStr = rtStr.Replace("%", "[%]");

            rtStr = rtStr.Replace(@"/", "////");

        }

        rtStr = rtStr.Replace("'", "''");

 

        return rtStr;

    }

 

 

 

===============================ppll的分割線==================================

 

查詢SqlServer特殊字元 原文：Here

 

 

我們都知道SQL查詢過程中，單引號“'”是特殊字元，所以在查詢的時候要轉換成雙單引號“''”。

但這隻是特殊字元的一個，在實際項目中，發現對於like操作還有以下特殊字元：底線“_”，百分比符號“%”，方括弧“[]”以及尖號“^”。

其用途如下：

底線：用於代替一個任一字元（相當於Regex中的 ? ）

百分比符號：用於代替任意數目的任一字元（相當於Regex中的 * ）

方括弧：用於轉義（事實上只有左方括弧用於轉義，右方括弧使用最近優先原則匹配最近的左方括弧）

尖號：用於排除一些字元進行匹配（這個與Regex中的一樣）

 

以下是一些匹配的舉例，需要說明的是，只有like操作才有這些特殊字元，=操作是沒有的。

a_b...	a[_]b%
a%b...	a[%]b%
a[b...	a[[]b%
a]b...	a]b%
a[]b...	a[[]]b%
a[^]b...	a[[][^]]b%
a[^^]b...	a[[][^][^]]b%

 

 

對於like操作，需要進行以下替換（注意順序也很重要）

[ -> [[]     (這個必須是第一個替換的!!)

% -> [%]    (這裡%是指希望匹配的字元本身包括的%而不是專門用於匹配的萬用字元)

_ -> [_]

^ -> [^]

 

 

===============================ppll的分割線==================================

引用：Here

SQL encode and decode Function2007-07-05 14:31

Function SQL_encode(strContent) If isnull(strContent) = False Then    strContent = replace(strContent, """", "&#34;")    strContent = replace(strContent, "'", "&#39;")    strContent = replace(strContent, "+", "&#43;")    strContent = replace(strContent, "*", "&#42;")    strContent = replace(strContent, "-", "&#45;")    strContent = replace(strContent, "=", "&#61;")    strContent = replace(strContent, "<", "&lt;")    strContent = replace(strContent, ">", "&gt;")    strContent = replace(strContent, "%", "&#37;")    strContent = replace(strContent, "_", "&#95;")    SQL_encode = strContent End If End Function Function SQL_decode(strContent) If isnull(strContent) = False Then    strContent = replace(strContent, "&#34;", """")    strContent = replace(strContent, "&#39;", "'")    strContent = replace(strContent, "&#43;", "+")    strContent = replace(strContent, "&#42;", "*")    strContent = replace(strContent, "&#45;", "-")    strContent = replace(strContent, "&#61;", "=")    strContent = replace(strContent, "&lt;", "<")    strContent = replace(strContent, "&gt;", ">")    strContent = replace(strContent, "&#37;", "%")    strContent = replace(strContent, "&#95;", "_")    SQL_Decode = strContent End If End Function edition 2006 ------------------------------------------------------------------- 'transform any SQL operators to their ascii equivalent function SQL_encode(strContent) if isnull(strContent) = false then    'transform sql operators to ascii equivalents    strContent = replace(strContent, "'", "|Q|")    strContent = replace(strContent, """", "|QQ|")    strContent = replace(strContent, "+", "|PLUS|")    strContent = replace(strContent, "*", "|STAR|")    strContent = replace(strContent, "-", "|MINUS|")    strContent = replace(strContent, "=", "|EQUALS|")    strContent = replace(strContent, "<", "|LEFT|")    strContent = replace(strContent, ">", "|RIGHT|")    strContent = replace(strContent, "%", "|PERCENT|")    strContent = replace(strContent, "_", "|UNDER|")    strContent = replace(strContent, "/", "|BACKS|")    strContent = replace(strContent, "/", "|FRONTS|")    SQL_encode = strContent end if end function 'tranform ascii characters to their SQL equivalent function SQL_decode(strContent) if isnull(strContent) = false then    'transform sql operators    strContent = replace(strContent, "|Q|", "'")    strContent = replace(strContent, "|QQ|", """")    strContent = replace(strContent, "|PLUS|", "+")    strContent = replace(strContent, "|STAR|", "*")    strContent = replace(strContent, "|MINUS|", "-")    strContent = replace(strContent, "|EQUALS|", "=")    strContent = replace(strContent, "|LEFT|", "<")    strContent = replace(strContent, "|RIGHT|", ">")    strContent = replace(strContent, "|PERCENT|", "%")    strContent = replace(strContent, "|UNDER|", "_")    strContent = replace(strContent, "|BACKS|", "/")    strContent = replace(strContent, "|FRONTS|", "/")    SQL_Decode = strContent end if end function