sql query integer in string

Question

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.

Gordon Linoff · Accepted Answer · 2017-01-21 13:53:48Z

1

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

answered Jan 21, 2017 at 13:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Ranadip Dutta Over a year ago

Will external type casting work like ` SELECT CONVERT('test', CHAR CHARACTER SET utf8); SELECT CAST('test' AS CHAR CHARACTER SET utf8);`

Gordon Linoff Over a year ago

@RanadipDutta . . . I believe that will return an error if the string contains non-digits.

Ranadip Dutta Over a year ago

Yes right. But I must say that silent conversion is great

Collectives™ on Stack Overflow

sql query integer in string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related