jacky jacky - 3 months ago 8
Java Question

How to get max integer value of a column in java sql?

Basically I have a Sql Column named phone, where it contains numbers and letter.
I want to get the max integer value of my phone table, I found that it is possible using

MAX()
but for me that return max letters not the number value.

Here is how I am doing it:

String query = "SELECT MAX(PHONE) FROM db.stb";


I need the output result to be this
00010511000000000
which is the maximum value in my column

and I really appreciate if you tell me how to specify my column name in my query, something like:

String query = "SELECT MAX(PHONE) FROM db.stb WHERE COLUMNNAME=PHONE";

Answer

As i mentioned in comments, you should not store two different information in a single column. Consider changing the table structure.

With this structure to get the result, first filter out the rows with alphabet on it. Use REGEXP to filter the records which has only numeric values. Then take the max value out of it.

SELECT MAX(PHONE) FROM db.stb WHERE col1 REGEXP '^[0-9]+$';

REGEX concept taken from this question : Detect if value is number in MySQL

Comments