CODEWITHSUNDEEP
mysqlsql
JdeHaan
JdeHaan

Reputation: 392

sql query integer in string

I have a database, that I can not modify, which contains in a table1 in a varchar(255) field1, an 32 bit integer value.

I am interested in the last ten, or such, records of said table from which the integer is smaller than a given parameter value.

Running the next sql statement almost works:

echo 'select field1 from table1 where field1 < 123456 order by field1;'|mysql

The problem is that field1 contains values like

123450
123453

which answer to the selection, but also values like

12344
12345

which also meet the criteria, because of the sort order and method used (sort with string?).

The problem of course is the lack of leading zero's, which I can not help.

What is the best way of solving this in a pure sql query way? Because of time constraints in the final solution, I prefer a single query per parameter.

Upvotes: 0

Views: 3592

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can convert the value by treating it as numeric. Here is a simple way:

select field1
from table1
where field1 + 0 < 123456
order by field1 + 0;

The + 0 does "silent conversion". If the string is not a number, then it returns 0. The conversion only applies to leading digits in the string.

In general, it is best to use the correct type. If the field contains non-numeric values, you might want:

where field1 + 0 < 123456 and
      field1 + 0 > 0

Upvotes: 1

Related Questions