title: Concatenation inside CONTAINS function in SQL Server
body: <p>Here is a simplified snippet of what I am trying to do:</p>
  
  <pre><code>SELECT StreetNumber + StreetName AS 'Address'
  FROM TABLE
  WHERE CONTAINS('Address', 'Street Name')
  </code></pre>
  
  <p>I realize that I cannot use an alias here since the WHERE clause is evaluated before the SELECT clause.</p>
  
  <p>The following two alternatives also do not work:</p>
  
  <p>1)</p>
  
  <pre><code>SELECT Street_Number + StreetName AS 'Address'
  FROM TABLE
  WHERE CONTAINS(CONCAT(StreetNumber, '', StreetName), 'Street Name')
  </code></pre>
  
  <p>2)</p>
  
  <pre><code>SELECT Street_Number + StreetName AS 'Address'
  FROM TABLE
  WHERE CONTAINS(StreetNumber + ' ' + StreetName), 'Street Name')
  </code></pre>
  
  <p>How can I accomplish this?</p>