CODEWITHSUNDEEP
sqlselectsyntaxvarcharbetween
Kuei
Kuei

Reputation: 35

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?

Upvotes: 2

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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.

Upvotes: 1

WileCau
WileCau

Reputation: 2197

You said the string is VARCHAR(6) and you want to select anything between 0-999999, so that seems to be anything numeric.

You can select the numeric values like this:

SELECT TOP 2000 *
FROM TABLE1
WHERE ISNUMERIC(SEQ) = 1

Or if you know there are only numeric strings and blanks (I assume by blank you mean NULL or ''), you could select the non-blanks like this:

SELECT TOP 2000 *
FROM TABLE1
WHERE SEQ IS NOT NULL AND SEQ <> ''

Upvotes: 0

Related Questions