title: Pattern matching on encrypted columns
body: <p>I'm not able to do pattern matching on encrypted column even in SQL Server 2019 version.</p>
  
  <p>SQL Server 2019    </p>
  
  <p><strong>BEFORE encryption</strong>    </p>
  
  <pre><code>select * 
  from messageencryption;     
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>id    msgcode   msg      
  -------------------------------------------
  1      AA56B    this is a text message     
  2      AA56C    this is a text123 message    
  3      AA56D    EXTENDED BOOTCAMP      
  4      AA56E    extended bootcamp       
  5      AA56J    advance happy new year       
  6      AA56K    oneteam       
  7      AA56L    cricket team consists of 11 players       
  8      AA56M    indian cricket team      
  
  select * 
  from messageencryption 
  where msg like '%team%';       
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>id   msgcode   msg              
  ----------------------------
  6    AA56K     onesmallteam                   
  7    AA56L     cricket team consists of 11 players                
  8    AA56M     indian cricket team               
  </code></pre>
  
  <p><strong>AFTER</strong> encryption:</p>
  
  <pre><code>select * 
  from messageencryption 
  where msg like '%team%';
  </code></pre>
  
  <p>Output                 </p>
  
  <blockquote>
    <p>Msg 402, Level 16, State 2, Line 23<br>
    The data types varchar(8000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
    column_encryption_key_name = 'cek', column_encryption_key_database_name = 'encrypt') collation_name = 'Latin1_General_BIN2' and varchar are incompatible in the like operator.      </p>
  </blockquote>
  
  <p>Expected output:</p>
  
  <pre><code>id   msgcode   msg              
  ----------------------------
  6    AA56K     onesmallteam                   
  7    AA56L     cricket team consists of 11 players                
  8    AA56M     indian cricket team               
  </code></pre>