title: SQL Server : remove character from string variable with CONTAINTABLE and JOIN
body: <p>I have a string variable in my SQL Server stored procedure. </p>
  
  <p>The string variable has a special character like '-' which I like to remove. </p>
  
  <p>Here is my current sql statement</p>
  
  <pre><code>declare @MyString varchar4(100) = '00-999'
  select [ProductId],[RANK] AS RANK0
  from MyTable mt
  join CONTAINSTABLE (MyTable, *, @MyString ) AS cnt
  on cnt.[KEY] = mt.ProductId
  </code></pre>
  
  <p>This query works but I want search via FullTextSearch for both 00-999 and 00999. 
  Basically do a fulltextsearch for both string value with and without the hyphen and combine the results as though I was doing a union.</p>
  
  <p>I tried this query but I am getting syntax error from sql server studio</p>
  
  <pre><code>select [ProductId],[RANK] AS RANK0
  from MyTable mt
  join CONTAINSTABLE (MyTable, *, @MyString ) AS cnt
    on cnt.[KEY] = mt.ProductId
  join CONTAINSTABLE (MyTable, *, replace(@MyString, '-', '') ) AS cnt
    on cnt.[KEY] = mt.ProductId
  </code></pre>
  
  <p>Any help is greatly appreciated. Thanks. Newbie</p>