The table has a column that is type varchar6 containing 6 numbers between 000000 to 999999, or is empty. No matter what values you give the various variables for this column SEQ, it keeps returning no results but no syntax errors. I suspect you can't find anything in SQL if you do a between with two varchar6's.
The sql query is;
select Top 2000 A.*
from TABLE1 A
where A.SEQ between '0' and '999999'
This feels like a design flaw because storing and comparing numbers probably shouldn't be using varchar6 but Changing the design of the table is unfortunately out of the question.
How do I change this SQL so that it does compare properly?
Your code should work. I suspect you have a problem with the data. You can check with:
select A.*
from TABLE1 A
where try_convert(int, A.SEQ) is null and A.SEQ is not null;
Or:
select A.*
from TABLE1 A
where A.seq like '%[^0-9]%';
When you find the problem with the data, you can fix the issue. You will also learn why you should NOT store numbers in a string.