title: SQL: Filter on Alpha and numeric on column of Type TEXT
body: <p>I've got a column of type Text.  In the column are numeric values such as<code>4, 8, 3.2, etc..</code>. and also values such as <code>'Negative', 'Positive', '27A', '2pos 1neg'.</code> </p>
  
  <p>The user needs to be able to say: "Give me all the values between 10 and 30, and also the values that are 'Negative'.  The <code>WHERE</code> clause would need to do something along the lines of: </p>
  
  <pre><code>WHERE Tbl.Col &gt; 10
  AND   Tbl.Col &lt; 30
  AND   Tbl.Col = 'Negative'
  </code></pre>
  
  <p>This is problematic for obvious reasons.  I've tried using the ISNUMERIC function to alleviate the issue but can't seem to get exactly what i need.  I can either get all the alpha values in the column, or all the numeric values in the column as floats but cant seem to filter on both at the same time.  To grab all the Numeric values I've been using this:</p>
  
  <pre><code>SELECT Num.Val FROM 
      (SELECT Val = CASE ISNUMERIC(CAST(TBL.COL AS VARCHAR)) WHEN 1 
               THEN CAST(CAST(TBL.COL AS VARCHAR) AS FLOAT) ELSE NULL END
              FROM Table TBL
              WHERE TBL.COL IS NOT NULL ) as Num
      WHERE Num.val IS NOT NULL
      AND   Num.val &gt; 10
  </code></pre>