MySQL column order by alphanumeric ignoring highest value-CodePudding

In MySQL I have a table named custom_meta where I am getting the values like

id   meta_value
1    USNEWYORK01
2    USNEWYORK02
3    USNEWYORK03
4    USNEWYORK04
5    USNEWYORK05
6    USNEWYORK06
7    USNEWYORK07
8    USNEWYORK10
9    USNEWYORK14
10   USNEWYORK16
11   USNEWYORK20
12   USNEWYORK21
13   USNEWYORK32
14   USNEWYORK45
15   USNEWYORK56
16   USNEWYORK78
17   USNEWYORK68
18   USNEWYORK69
19   USNEWYORK80
20   USNEWYORK90
21   USNEWYORK99
22   USNEWYORK100
23   USNEWYORK45
24   USNEWYORK101

Now I want to get the meta_value with highest number. So in the table you can see the highest one is USNEWYORK101. So to get that I have made query like this

SELECT meta_value from custom_meta ORDER BY meta_value DESC LIMIT 1

But it is getting USNEWYORK99 always. I have tried to use CAST also but that is also not working.

So can someone help me to get this done? Any suggestions and advice would be really appreciable.

Thanks.

CodePudding user response：

SELECT id,
       meta_value,
       Cast(Substring(meta_value, 10, Length(meta_value)) AS UNSIGNED)
FROM   custom_meta
ORDER  BY 3 DESC
LIMIT  1;

CodePudding user response：

For newer versions of MySQL you could use REGEXP_SUBSTR() function, but since you're using 5.7 we could do it some other way.

Assuming that you always have USNEWYORK at the beginning of your values, you could get rid of it, leaving only what comes after that using SUBSTRING() function. Then you need to perform CAST() to convert text to number values so that sorting works properly.

Sample data

create table custom_meta(meta_value varchar(255));

insert into custom_meta(meta_value) values
('USNEWYORK01'),('USNEWYORK02'),('USNEWYORK45'),('USNEWYORK99'),('USNEWYORK101');

Solution

select
    meta_value
from
  custom_meta
order by
  cast(substring(meta_value, 10) as unsigned) desc
limit 1

Output

meta_value 
USNEWYORK101

CodePudding user response：

Replace and cast it to int like this:

IF Prefix is FİXED to USNEWYORK:

select * from test 
    order by  CAST(REPLACE(meta_value, "USNEWYORK", "") AS SIGNED) desc;

IF Prefix is not fixed and can be anything , and mysqldb 8.0

select * from test 
    order by  CAST(REGEXP_REPLACE(meta_value, '[^0-9]', '') AS SIGNED) desc;

IF Prefix is not fixed and can be anything , and mysqldb 5.7 You can create this function that explained here and use it like this:

select * from test 
        order by  CAST(STRIP_NON_DIGIT(meta_value) AS SIGNED) desc;

CodePudding user response：

Another way of doing for MySQL 5.7 and only if the string include the capital alphabetical letters is :

    select meta_value,
      cast(replace
           (replace
            (replace
             (replace
              (replace
               (replace 
                (replace
                 (replace
                  (replace
                   (replace
                    (replace
                     (replace
                      (replace
                       (replace
                        (replace
                         (replace                                         
                          (replace
                           (replace
                            (replace
                             (replace
                              (replace
                               (replace
                                (replace
                                 (replace
                                  (replace
                                   (replace(meta_value,'A','')
                                                     ,'B','')
                                                    ,'C','')
                                                   ,'D','')
                                                  ,'E','')
                                                 ,'F','')
                                                ,'G','')
                                               ,'H','')
                                              ,'I','')
                                             ,'J','')
                                            ,'K','')
                                           ,'L','')
                                          ,'M','')
                                         ,'N','')
                                        ,'O','')
                                       ,'P','')
                                      ,'Q','')
                                     ,'R','')
                                    ,'S','')
                                   ,'T','')
                                  ,'U','')
                                 ,'V','')
                                ,'W','')
                               ,'X','')
                              ,'Y','')
                             ,'Z','') 
                        as UNSIGNED) 
         as nr  
from test 
order by nr desc;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/38