CODEWITHSUNDEEP
sql
mwok
mwok

Reputation: 257

String greater than or less than

I have the following data:

[sequences]
/a1
/a2
/a3
...
/a10

The query SELECT * FROM sequences WHERE nbr <= '/a10' should return the list above, instead it returns:

[results]
/a1
/a10

How do I make it return all the rows in the above list?

Upvotes: 7

Views: 27097

Answers (2)

user unknown
user unknown

Reputation: 36229

SELECT *
FROM sequences 
WHERE toInt(substring (nbr, 2)) <= 10;

Name and syntax of 'substring'-function and 'toInt' will vary from db-implementation to db-implementation.

Upvotes: 2

GolezTrol
GolezTrol

Reputation: 116110

It works as it should. To compare the numeric value, you'll have to convert these to numbers somehow. A good start would be to use substr(yourfieldname, 3) to cut of the/a. Then you can useconvert` to typecast it to int, so your final query will look something like:

select * from sequences where convert(int, substr(nbr, 3)) <= 10

Mind that the exact functions and rules for converting strings to ints may very per dbms. This illustrates the general idea, though.

Upvotes: 7

Related Questions