How to get the max value from a column with alphanumeric strings?

Question

I have a table which contains invoice and estimate numbers.The invoice numbers are like "IN1000","IN1001","IN1002" and so on.The estimate numbers are like "ES101","ES102","ES103".How can I get the max of both my invoice and estimate? I would also like it to be cast into an integer like 1000.

I have tried the following query:

SELECT Max(CAST (SUBSTR(invoiceNo,3) AS UNSIGNED)) FROM selected_items 
WHERE invoiceNo RLIKE 'IN';

When I run this query I get the following error :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED)) FROM selected_items WHERE invoiceNo RLIKE 'IN'' at line 1.

And I am using similar approach for estimate :

SELECT Max(CAST (SUBSTR(invoiceNo,3) AS UNSIGNED)) FROM selected_items
WHERE invoiceNo RLIKE 'ES';

How can I do both the operations in one query? Any help is appreciated.Thank you.

Thanks for the edit @FelixPamittan can you help with a solution? — AndroidNewBee, Feb 05 '16 at 05:38
No, I only saw this question because it showed up under a tag I follow. — Aaron Bertrand, Feb 05 '16 at 05:40
I suspect that your `invoiceNo` column may have some extra characters in it. This is why the `CAST` is failing. Can you show us some sample data? — Tim Biegeleisen, Feb 05 '16 at 05:40
I recommend that you strip out alpha characters. Take a look at this post http://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare — Tah, Feb 05 '16 at 05:42
@TimBiegeleisen Yes both invoice number and estimate number get saved in the same column like "IN1000" and estimates like "ES100" how can I overcome this I am new to programming. — AndroidNewBee, Feb 05 '16 at 05:43
is the prefix constant or the same? is it always "IN" and "ES"? — Ceeee, Feb 05 '16 at 05:44
@Ceeee yes the prefix will always be either "IN" or "ES" where "IN" stands for Invoice Number and "ES" stands for Estimate number. — AndroidNewBee, Feb 05 '16 at 05:46
you can get max value from alphanumeric column by breaking string in two variables (character ,number) then get order by desc value and again join them — Ankit Agrawal, Feb 05 '16 at 05:47

score 2 · Answer 1 · answered Feb 05 '16 at 05:57

As Suggested by @FuzzyTree and @TimBiegeleisen you can try the following query .

Select (SELECT MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'IN') as maxIN,
(SELECT MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'ES') as maxES;

score 1 · Accepted Answer · edited May 23 '17 at 11:58

I believe that your invoiceNo column has some extra characters which are still present even after you use SUBSTR(). This is causing the CAST to fail because non numeric characters are still present. If these extra characters be whitespace, then the TRIM() function might come in handy:

SELECT MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'IN'

As @Tah pointed out, here is a link which might help you to remove all alphanumeric characters, if it comes to that:

MySQL strip non-numeric characters to compare

Update:

If you want to get both max values in one query, one way would be to do a UNION of the two queries you mentioned:

SELECT 'invoiceMax', MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'IN'
UNION
SELECT 'estimateMax', MAX(CAST(SUBSTR(TRIM(invoiceNo),3) AS UNSIGNED))
FROM selected_items 
WHERE invoiceNo RLIKE 'ES'

Ya this works but how can I do the same thing for "ES" without writing a new query. — AndroidNewBee, Feb 05 '16 at 05:48

score 1 · Answer 3 · answered Feb 05 '16 at 05:48

You can combine your queries using conditional aggregation:

select
    max(case when invoiceNo rlike 'IN' then (your value here) end) maxIn,
    max(case when invoiceNo like 'ES' then (your value here) end) maxEs
from selected_items
where invoiceNo rlike 'IN'
or invoiceNo like 'ES'

Another way is to use subqueries. I'm not sure which runs faster, so you might want to test both.

select
    (your in query) as maxIn,
    (your en query) as maxEn

Thank you so much @Fuzzytree :) – AndroidNewBee Feb 05 '16 at 06:00 — AndroidNewBee, Feb 05 '16 at 06:00

score 0 · Answer 4 · answered Sep 07 '18 at 04:47

0

// get max index value   codeinigter
        $res = $this->db->get($table)->result();
        $arr = array();
        foreach ($res as $val) {
            $str = preg_replace('/\D/', '', $val->id);
            $arr[] = $str;
        }
        $or = max($arr) + 1;

answered Sep 07 '18 at 04:47

Kuldipsinh Parmar

1
2

1

Can you add some explanation to your code such that others can learn from it? Additionally, can you explain why you introduce PHP code to a question that is already answered multiple times with pure MySQL queries? – Nico Haase Sep 07 '18 at 07:52

score 0 · Answer 5 · edited Jan 14 '23 at 12:49

0

Try this:

SELECT
  MAX(invoiceNo)
FROM
  public.selected_items
WHERE
  LENGTH(invoiceNo) = (
    SELECT 
      MAX(LENGTH(invoiceNo))
    FROM
      public.selected_items
  )

It should work for PostgreSQL also.

edited Jan 14 '23 at 12:49

cursorrux

1,382
4
9
20

answered Jan 10 '23 at 19:53

Nabyendu

1
2

How to get the max value from a column with alphanumeric strings?

5 Answers5