Querying SQL column varchar6 containing numbers with a between statement

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?

Answer

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.