I need a help for Database select * sorting-CodePudding

here is my table SQL Query

CREATE TABLE IF NOT EXISTS tea_suppliers
(
    Suppliers_ID VARCHAR(10) NOT NULL UNIQUE,
    Name         VARCHAR(45) NOT NULL,
    ID           VARCHAR(12) NOT NULL UNIQUE,
    Address      VARCHAR(45) NOT NULL,
    Mobile_No    VARCHAR(10) NULL DEFAULT NULL,
    PRIMARY KEY (Suppliers_ID)
);

I need to display S01,S02,...,S09,S010,S011 like pattern. But select * from display pattern like S01,S010,S011,S02,...,S09 (see image) Please tell there is any way to do that

CodePudding user response：

You may sort on only the numeric portion of the Suppliers_ID:

SELECT *
FROM tea_suppliers
ORDER BY CAST(SUBSTRING(Suppliers_ID, 2) AS UNSIGNED);

Note that a better table design would be to make Suppliers_ID a pure numeric column. Then you could simply order using this column without making a cast.