title: SQL Conversion Issue: Comparing selected part of VarChar and money
body: <p>I have a table that I cannot modify that I need to do a comparison between 2 columns on.</p>
  <ul>
  <li>The first column <code>TtlPrice</code> has a <code>money</code> datatype</li>
  <li>The second <code>AuthComments</code> has a <code>varchar(max)</code> datatype</li>
  </ul>
  <p>We are typing a comment in the <code>AuthComments</code> column and at the end, adding a &quot;$1.00&quot; (or whatever price we are authorizing). I want to make sure that the <code>TtlPrice</code> isn't higher than the price in the <code>AuthComment</code> field.</p>
  <p>Here is a sample of the data:</p>
  <pre class="lang-none prettyprint-override"><code>OrderNo | TtlPrice | AuthComment
  --------+----------+----------------------
  12345   | 51.74    | Auth by Sue- $62.84
  </code></pre>
  <p>Here is my current query:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
      OrderNo, 
      TtlPrice, 
      AuthComment 
  FROM     
      tableA
  WHERE
      (CAST(TtlPrice AS VarChar) &gt; CAST(SUBSTRING(AuthComments,
                                                  CHARINDEX('$', AuthComments) + 1,
                                                  LEN(AuthComments) - CHARINDEX('$', AuthComments)) AS varchar))
  </code></pre>
  <p>The above, based on the data, should yield 0 results, because <code>TtlPrice</code> is less than the <code>AuthComment</code> price. But it is not, it is yielding 1 line result. I think it is not comparing the <code>varchar</code> properly, but trying to convert to any sort of numeric (int, money, etc) throws a conversion error. Suggestions?</p>