Sqlserver special character conversion & Query

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 StringSqlencode (StringStrvalue,BoolIslikestatement)

{

StringRtstr = strvalue;

If(Islikestatement)

{

Rtstr = strvalue. Replace ("[","[[]");// This sentence must be at the beginning

Rtstr = rtstr. Replace ("_","[_]");

Rtstr = rtstr. Replace ("%","[%]");

Rtstr = rtstr. Replace (@"/","////");

}

Rtstr = rtstr. Replace ("'","''");

 

ReturnRtstr;

}

 

 

 

====================================== Ppll split line ======== ======================================

 

Query the original special characters of sqlserver: here

 

 

We all know that the single quotation mark (') is a special character in the SQL query process. Therefore, it must be converted into double single quotation marks ('') during the query.

However, this is only a special character. In actual projects, we found that the like operation has the following special characters: underscore "_", percent "% ", square brackets "[]" And tip "^ ".

Its usage is as follows:

Underline: Used to replace any character (equivalent? )

Percent: Used to replace any number of characters (equivalent to * in a regular expression *)

Square brackets: used for escape (in fact, only the left square brackets are used for escape, and the right square brackets use the recent priority principle to match the nearest left square brackets)

Tip: used to exclude some characters for matching (this is the same as in regular expressions)

 

Here are some examples of matching. It should be noted that only the like operation has these special characters, and the = operation does not exist.

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 %

 

 

For the like operation, you need to replace the following (note that the order is also important)

[-> [[] (This must be the first replacement !!)

%-> [%] (Here % refers to the % of the characters to be matched, rather than the wildcard specifically used for matching)

_-> [_]

^-> [^]

 

 

====================================== Ppll split line ======== ======================================

Reference: here

SQL encode and decode Function2007-07-05

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