SQL/JPQL 'Between' for numbers represented as strings

Question

I have a table where numbers are represented as Strings.

Is there a way to make an SQL/JPQL between query function as though they are Integers?

Currently between 100 and 110 returns 1001, 1002, 1015 etc. It should return only the numbers between 100 and 110.

The column type cannot be changed.

Not me, I'm just taking a look at it. It is a part of a pretty old product and nobody's sure why it was done. — cmn
– cmn, Commented Mar 18, 2014 at 7:06

shree.pat18 · Accepted Answer · 2014-03-18 07:02:32Z

1

You could try casting the value column as a numeric datatype before doing the comparison, like so:

SELECT *
FROM YOURTABLE
WHERE CAST(VALUE AS INT) BETWEEN 100 AND 110

This will ensure that comparison is done on numeric values, not text.

If you are passing parameters of VARCHAR data type as parameters, you will also need to explicitly cast them like so:

SELECT *
FROM YOURTABLE
WHERE CAST(VALUE AS INT) BETWEEN (CAST @Lower AS INT) AND (CAST @Upper AS INT)

answered Mar 18, 2014 at 7:02

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1