title: Removing smart quotes from an SQL server text column
body: <p><strong>Problem</strong>: My company is using Liferay 6.0 which being upgraded to Liferay 6.1 GA2. In the 6.0 we have a large number of rows in an SQL server database column that have smart quote characters. In the automatic upgrade the smart quotes are being replaced by a '?'. We were planning to run a find and replace SQL statement on the content before upgrading to prevent this issue. I wrote a small Java program to find the ASCII values of the character. The character appears differently in ANSI ("Aâ€ŽA") than in UTF-8 ("A‎A"). I have sandwiched the smart quote character between capital A's in the preceding sentence.</p>
  
  <pre><code>public class CheckAscii {
      public static void main(String args[]) {
          asciiVals("Aâ€ŽA"); //ANSI
          asciiVals("A‎A"); //UTF-8
      }
      static void asciiVals(String str) {
          System.out.println("Length of the string: " + str + " is " + str.length());
          for (int j=0; j&lt;str.length(); j++){
              System.out.println("Ascii value of char " + str.charAt(j) + " : " + (int)str.charAt(j));
          }
          System.out.println("");
      }
  }
  </code></pre>
  
  <p>The output was </p>
  
  <p>Length of the string: Aâ€ŽA is 5<br>
  Ascii value of char A : 65<br>
  Ascii value of char â : 226<br>
  Ascii value of char € : 8364<br>
  Ascii value of char Ž : 381<br>
  Ascii value of char A : 65<br>
  <br>
  Length of the string: A‎A is 3<br>
  Ascii value of char A : 65<br>
  Ascii value of char ‎ : 8206<br>
  Ascii value of char A : 65<br>
  <br></p>
  
  <p>The below queries did not give me any rows,</p>
  
  <pre><code>SELECT [COLUMN] FROM [TABLE] where [COLUMN] like '%['+char(226)+']%'
  SELECT [COLUMN] FROM [TABLE] where [COLUMN] like '%['+char(8364)+']%'
  SELECT [COLUMN] FROM [TABLE] where [COLUMN] like '%['+char(381)+']%'
  SELECT [COLUMN] FROM [TABLE] where [COLUMN] like '%['+char(8206)+']%'
  </code></pre>
  
  <p>This query fetches,</p>
  
  <pre><code>select CHAR(226), CHAR(8364), CHAR(381), CHAR(8206)
  
  â null null null
  </code></pre>
  
  <p>I don't how to look for those characters in the text. Does anyone know how to form a search query for the smart quotes in SQL Server?</p>