SQL Query varchar field with numeric search

Question

I created a table that had a nvarchar field named 'Notes', which people can enter whatever they want (number or text) to help for their searching.

For example

Table "Customer"
ID  Name  Notes
1   AAA   1234
2   BBB   1235
3   CCC   1236
4   DDD   ABCD

From this data, I would like to write sql query for Notes only between 1200 to 1300 however it won't allow me to do so as this field also contain text value.

I tried this

SELECT * 
FROM Customer
WHERE ISNUMERIC(Notes) = 1 AND Notes > 1200 AND Notes < 1300

Error: Conversion failed when converting the nvarchar value to int

and then I tried this, which I though that will work, but same error show up

SELECT *
FROM
 (SELECT *
  FROM Customers
  WHERE ISNUMERIC(Notes) = 1 
 ) A
WHERE A.Notes > 1200 AND A.Notes < 1300

Can someone help? Thanks alot

representing a numeric value in varchar is really bad practice. think hard about why you would need that, can't you use another column just redundantly. — zinking, Jan 28 '14 at 06:02

score 1 · Accepted Answer · answered Jan 28 '14 at 01:57

Try this:

SELECT * 
FROM Customer
WHERE (case when ISNUMERIC(Notes) = 1 then cast(Notes as float) end) > 1200 AND
      (case when ISNUMERIC(Notes) = 1 then cast(Notes as float) end) < 1300

This works because the case guarantees the order of evaluation in this case. You could also write this with a subquery:

select *
from (select c.*,
             (case when ISNUMERIC(Notes) = 1 then cast(Notes as float) end) as NotesNum
      from Customer c
     ) c
where NotesNum > 1200 and NotesNum < 1300;

Alternatively, this might come close to what you want:

SELECT * 
FROM Customer
WHERE ISNUMERIC(Notes) = 1 AND
      Notes > '1200' AND Notes < '1300' and len(notes) = 4 and notes not like '%.%'

SQL Query varchar field with numeric search

1 Answers1