1

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";
user3678528
  • 1,741
  • 2
  • 18
  • 24
jacky
  • 199
  • 1
  • 4
  • 15

1 Answers1

2

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

Community
  • 1
  • 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • I just did the way you said and the result was: `john.fobmate` – jacky Sep 04 '16 at 11:30
  • @jacky Ok, Try this `SELECT PHONE FROM db.stb WHERE col1 REGEXP '[0-9]+'` and tell whether you are getting any non-numeric data in result – Pரதீப் Sep 04 '16 at 11:31
  • Ok that printed the integer value but not the max value – jacky Sep 04 '16 at 11:34
  • okay i got the point, change the `john.fobmate` to `john.fobmate1` and you will see the result, and in your table i want the result to be `234` – jacky Sep 04 '16 at 11:42
  • @jacky Fact is I don't know anything about `REGEX` in `Mysql`. Just did a better google search ;) Start using google you can do wonders – Pரதீப் Sep 04 '16 at 11:47