Beautify Integer values for comparing as strings

Question

Hi.

For some reason I need to enter my integer values to database as string, then I want to run a query on them and compare those integers as strings. Is there any way to beautify integer numbers (between 1 and 1 US billion as an example) so I can compare them as strings?

Thanks in advance.

Answer 1

Just add as much leading zeros as necessary

So the numbers 1,2,3 become in string form: 0000000001, 0000000002, 0000000003 This way string comparison goes correct

In java you'd use:

 String.format("%09d", number); 

to have 9 leading zeroes.

Answer 2

Another option would be to modify your query to convert the string value back to an integer. (Pseudcode below, may not compile/ work for your database)

String sql = " SELECT * "
+ " FROM some_table "
+ " WHERE CAST(some_table.int_column_stored_as_string AS INTEGER) = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInteger(1, myIntValue);

One problem with this is that because this runs a function on each row, the database will be unable to use any indices defined on some_table.int_column_stored_as_string.