1 Topic by tcoton (edited by tcoton 2015-04-28 14:27:40)

  • Registered: 2014-06-25
  • Posts: 863

Topic: How to get max integer value in a text column?

Hi there,

I am struggling in getting the max value of an integer in a Text column.

The TelNr column has to contain text and numbers but I would like to retrieve a very specific value from this column.

As shown in the attached file, the numbers are not sorted in the right order and thus when I request the max value, I do not get the right result.

The point is that in my application, I have a query to check the next available telephone number depending on which number is already in the database BUT I want to limit the availability on the last 4 digits and it does not work due to the text type of this column.

Any suggestion apart from a re-design of the database and the application?

http://myvisualdatabase.com/forum/misc.php?action=pun_attachment&item=905

Post's attachments

Attachment icon sql_pb.png 103.08 kb, 381 downloads since 2015-04-28 

  • mr_d
  • Member
  • Offline
  • Registered: 2014-11-07
  • Posts: 54

Re: How to get max integer value in a text column?

the problem as you suspect is that the values stored in your table column are text and not integers; so to get the max value of the number starting at the 5th digit, you have to convert the text into an integer and perform the max function on that instead of the text (and the same in your check):
so it would look something like the following:

select max(strtoint(substr(t1.TelNr,-5 5))) from Telephony t1 where strtoint(substr(t1.TelNr,-5 5)) < 5560

and depending on what you are doing with the result, you may have to convert it back to a string.

Dennis
  • Registered: 2014-06-25
  • Posts: 863

Re: How to get max integer value in a text column?

Thanks mr_d but when I run this query in SQLiteStudio, I get :[15:11:41] Error while executing SQL query: no such function: strtoint

select max(strtoint(substr(t1.TelNr,-5, 5))) from Telephony t1 where strtoint(substr(t1.TelNr,-5, 5)) < 5560
  • Registered: 2014-06-25
  • Posts: 863

Re: How to get max integer value in a text column?

The most approaching coherent result I could get is with this query but I cannot manage the substr within:

select max(CAST(TelNr as integer)) as NB from Telephony t1
where t1.TelNr < 5987
and TelNr not in (' ','Basestation','Reserved')
and TelNr is not null
order by NB
  • mr_d
  • Member
  • Offline
  • Registered: 2014-11-07
  • Posts: 54

Re: How to get max integer value in a text column?

the function strtoint does not seem to be valid for sql statements, but thanks to your use of cast, i was able to get an example working for you - please have a look at the attached project (you will have to build the exe file or run in the ide).

Post's attachments

Attachment icon max_of_string_numbers(MVD).zip 335.83 kb, 743 downloads since 2015-04-30 

Dennis
  • Registered: 2014-06-25
  • Posts: 863

Re: How to get max integer value in a text column?

Mr_D, it seems to work within your application thanks a lot, I will a have a deeper look at it when trying to integrate that inside the massive SQL query I have around this specific one wink This is just the tiny part on which I was struggling a lot, many thanks for your time!