转义转义字符不起作用 – SQL LIKE 运算符

Question

我使用作为LIKE运算符的escape character。我正在转义以下四个字符

1 % 2 [ 3 ] 4 _

当我将转义字符作为输入传递时，查询不返回值。我怎样才能让它工作？

数据插入

DECLARE @Text VARCHAR(MAX)
SET @Text = 'Error \ \  C:toolboxline 180'
INSERT INTO Account (AccountNumber,AccountType,Duration,ModifiedTime) 
VALUES (198,@Text,1,GETDATE())

法典

    static void Main(string[] args)
    {
        string searchValue1 = @"Error \ \  C:toolboxline 180";
        string searchValue2 = @"55555";
        string result1 = DisplayTest(searchValue1);
        string result2 =  DisplayTest(searchValue2);
        Console.WriteLine("result1:: " + result1);
        Console.WriteLine("result2:: " + result2);
        Console.ReadLine();
    }}

     private static string DisplayTest(string searchValue)
    {
        searchValue = CustomFormat(searchValue);

        string test = String.Empty;
        string connectionString = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string commandText = @"SELECT AccountType,* 
                              FROM Account 
                              WHERE AccountType LIKE @input ESCAPE ''";
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddWithValue("@input", "%" + searchValue + "%");
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            test = reader.GetString(0);
                        }
                    }
                }
            }
        }
        return test;
    }

    private static string CustomFormat(string input)
    {
        input = input.Replace(@"%", @"%");
        input = input.Replace(@"[", @"[");
        input = input.Replace(@"]", @"]");
        input = input.Replace(@"_", @"_");
        //input = input.Replace(@"", @"\");
        return input;
    }

参考：

1. 如何转义 LIKE 子句中的方括号？
2. 如何转义字符串以与SQL Server中的LIKE运算符一起使用？

Answer 1

像这样修改CustomFormat方法：

private static string CustomFormat(string input)
{
    input = input.Replace(@"", @"\"); 
    input = input.Replace(@"%", @"%");
    input = input.Replace(@"[", @"[");
    input = input.Replace(@"]", @"]");
    input = input.Replace(@"_", @"_");
    return input;
}

Answer 2

用于 LIKE 处理的 C# 代码

     public static string WildcardFormatSpecialCharacter(string source)
    {
        string formattedResult = string.Empty;
        if (!String.IsNullOrEmpty(source))
        {
            //Escape the escape character
            formattedResult = source.Replace(DataLayerConstants.EscapeCharacter, DataLayerConstants.EscapeCharacterWithEscape);
            //The %
            formattedResult = formattedResult.Replace(DataLayerConstants.Percentage, DataLayerConstants.PercentageWithEscape);
            //The [
            formattedResult = formattedResult.Replace(DataLayerConstants.OpenSqaureBracket, DataLayerConstants.OpenSqaureBracketWithEscape);
            //The ]
            formattedResult = formattedResult.Replace(DataLayerConstants.CloseSqaureBracket, DataLayerConstants.CloseSqaureBracketWithEscape);
            //The _
            formattedResult = formattedResult.Replace(DataLayerConstants.Underscore, DataLayerConstants.UnderscoreWithEscape);
        }
        return formattedResult;
    }

    public const string EscapeCharacter = @"";
    public const string EscapeCharacterWithEscape = @"\";
    public const string Percentage = "%";
    public const string PercentageWithEscape = @"%";
    public const string OpenSqaureBracket = "[";
    public const string OpenSqaureBracketWithEscape = @"[";
    public const string CloseSqaureBracket = "]";
    public const string CloseSqaureBracketWithEscape = @"]";
    public const string Underscore = "_";
    public const string UnderscoreWithEscape = @"_";

要检查的其他事项 在 SQL 查询中对换行符/回车符使用 REPLACE