Question 222848 in Mysql Question posted in
The official MySQL documentation can be found here.

Alphanumeric Filename Natural Sorting in MYSQL

I am working in content management of an E-Book publication database. Text contents are stored in eb_contents (VARCHAR) TABLE and accessed by page_name (VARCHAR). My page_name value like below –

EBook-00GUKP1142-10.jpg
EBook-00GUKP1142-11.jpg
EBook-00GUKP1142-12.jpg
EBook-00GUKP1142-7.jpg
EBook-02GUKP1375-1.jpg
EBook-02GUKP1375-4.jpg
EBook-02GUKP1375-5.jpg
EBook-02GUKP1375-52.jpg
EBook-02GUKP1375-53.jpg
EBook-02GUKP1375-56.jpg
EBook-02GUKP1375-57.jpg
EBook-02GUKP1375-6.jpg
EBook-04GUKP2577-17.jpg
EBook-04GUKP2577-26.jpg
EBook-04GUKP2577-27.jpg
EBook-04GUKP2577-28.jpg
EBook-04GUKP2577-68.jpg
EBook-04GUKP2577-69.jpg
EBook-05GUKP1661-1.jpg

I have tried implementing different sorting methods like below. But unable to get the result as expected. Any help is most welcome.

SELECT * FROM `eb_contents` ORDER BY Cast(page_name as Unsigned), page_name;
Tags:

2

Answers


  1. 0

    For your input only
    Try This

    SELECT *
FROM `eb_contents`
ORDER BY 
  SUBSTRING_INDEX(page_name, '-', 1),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(page_name, '-', -2), '-', 1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(page_name, '-', -1) AS UNSIGNED),
  page_name;

    output

    page_name
    EBook-00GUKP1142-7.jpg
    EBook-00GUKP1142-10.jpg
    EBook-00GUKP1142-11.jpg
    EBook-00GUKP1142-12.jpg
    EBook-02GUKP1375-1.jpg
    EBook-02GUKP1375-4.jpg
    EBook-02GUKP1375-5.jpg
    EBook-02GUKP1375-6.jpg
    EBook-02GUKP1375-52.jpg
    EBook-02GUKP1375-53.jpg
    EBook-02GUKP1375-56.jpg
    EBook-02GUKP1375-57.jpg
    EBook-04GUKP2577-17.jpg
    EBook-04GUKP2577-26.jpg
    EBook-04GUKP2577-27.jpg
    EBook-04GUKP2577-28.jpg
    EBook-04GUKP2577-68.jpg
    EBook-04GUKP2577-69.jpg
    EBook-05GUKP1661-1.jpg
    Login or Signup to reply.
  2. 0

    You need to convert to an numeric type to get the correct ordering.

    Schema (MySQL v5.7)

    CREATE TABLE test (
  pv varchar(255)
);
INSERT INTO test VALUES
('EBook-00GUKP1142-10.jpg'),
('EBook-00GUKP1142-11.jpg'),
('EBook-02GUKP1375-57.jpg'),
('EBook-02GUKP1375-6.jpg'),
('EBook-04GUKP2577-17.jpg'),
('EBook-04GUKP2577-26.jpg'),
('EBook-04GUKP2577-27.jpg'),
('EBook-04GUKP2577-28.jpg'),
('EBook-00GUKP1142-12.jpg'),
('EBook-00GUKP1142-7.jpg'),
('EBook-02GUKP1375-1.jpg'),
('EBook-02GUKP1375-4.jpg'),
('EBook-02GUKP1375-5.jpg'),
('EBook-02GUKP1375-52.jpg'),
('EBook-02GUKP1375-53.jpg'),
('EBook-02GUKP1375-56.jpg'),
('EBook-04GUKP2577-68.jpg'),
('EBook-04GUKP2577-69.jpg'),
('EBook-05GUKP1661-1.jpg')

    Query #1

    SELECT substring(pv,7,2),substring(pv,13,4), convert(substring(pv,18,length(pv)-21),decimal) from test;
    substring(pv,7,2) substring(pv,13,4) convert(substring(pv,18,length(pv)-21),decimal)
    00 1142 10
    00 1142 11
    02 1375 57
    02 1375 6
    04 2577 17
    04 2577 26
    04 2577 27
    04 2577 28
    00 1142 12
    00 1142 7
    02 1375 1
    02 1375 4
    02 1375 5
    02 1375 52
    02 1375 53
    02 1375 56
    04 2577 68
    04 2577 69
    05 1661 1

    Query #2

    SELECT pv from test order by substring(pv,7,2),substring(pv,13,4), convert(substring(pv,18,length(pv)-21),decimal);
    pv
    EBook-00GUKP1142-7.jpg
    EBook-00GUKP1142-10.jpg
    EBook-00GUKP1142-11.jpg
    EBook-00GUKP1142-12.jpg
    EBook-02GUKP1375-1.jpg
    EBook-02GUKP1375-4.jpg
    EBook-02GUKP1375-5.jpg
    EBook-02GUKP1375-6.jpg
    EBook-02GUKP1375-52.jpg
    EBook-02GUKP1375-53.jpg
    EBook-02GUKP1375-56.jpg
    EBook-02GUKP1375-57.jpg
    EBook-04GUKP2577-17.jpg
    EBook-04GUKP2577-26.jpg
    EBook-04GUKP2577-27.jpg
    EBook-04GUKP2577-28.jpg
    EBook-04GUKP2577-68.jpg
    EBook-04GUKP2577-69.jpg
    EBook-05GUKP1661-1.jpg

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search