title: Escaping the escape character does not work – SQL LIKE Operator
body: <p>I have used <code>\</code> as <code>escape character</code> for <code>LIKE</code> operator. I am escaping following four characters</p>
  
  <p>1 <code>%</code> 2 <code>[</code>  3 <code>]</code> 4 <code>_</code></p>
  
  <p>When I pass the escape character as input, the query does not return a value. How can I make it work?</p>
  
  <p><strong>Data Insert</strong></p>
  
  <pre><code>DECLARE @Text VARCHAR(MAX)
  SET @Text = 'Error \\\ \\  C:\toolbox\line 180'
  
  INSERT INTO Account (AccountNumber,AccountType,Duration,ModifiedTime) 
  VALUES (198,@Text,1,GETDATE())
  </code></pre>
  
  <p><strong>CODE</strong></p>
  
  <pre><code>    static void Main(string[] args)
      {
  
          string searchValue1 = @"Error \\\ \\  C:\toolbox\line 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;
      }
  </code></pre>
  
  <p>REFERENCE:</p>
  
  <ol>
  <li><a href="https://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause">How can I escape square brackets in a LIKE clause?</a></li>
  <li><a href="https://stackoverflow.com/questions/1428197/how-to-escape-a-string-for-use-with-the-like-operator-in-sql-server">How to escape a string for use with the LIKE operator in SQL Server?</a></li>
  </ol>