MySQL order by first word in string

Question

My MySQL query gets motorcycle model names sorted by name SELECT * FROM models ORDER BY name i.e.:

[
'1200 Superenduro'
'250 ETC Enduro, silver',
'350 ETC 2014',
'450 ETC 2014',
'50 EC-X',
'690 ETC 2014',
'RS 1200 Superenduro'
]

The first word in the string is usually a number with engine capacity and I'd like to sort by this capacity. The expected output should be:

[
'50 EC-X',
'250 ETC Enduro, silver',
'350 ETC 2014',
'450 ETC 2014',
'690 ETC 2014',
'1200 Superenduro'
'RS 1200 Superenduro'
]

I tried to ORDER BY the first word, but looks like it doesn't really work: SELECT * FROM models ORDER BY SUBSTRING_INDEX(name, " ", 1). How to achieve proper sorting? I use MySQL 5.7. I may manipulate the output in PHP.

In the absence of a database solution, perhaps this csn help https://www.php.net/manual/en/function.natsort.php — Tim Morton, Jan 14 '21 at 15:03
Does this answer your question? [How to find the first number in a text field using a MySQL query?](https://stackoverflow.com/questions/9395178/how-to-find-the-first-number-in-a-text-field-using-a-mysql-query) — kmoser, Jan 16 '21 at 17:57

score 0 · Answer 1 · answered Jan 14 '21 at 14:29

0

In MySQL, you can extract the first sequence of digits and convert to a number:

order by regexp_substr(name, '[0-9]+') + 0

Here is a db<>fiddle.

answered Jan 14 '21 at 14:29

Gordon Linoff

1,242,037
58
646
786

Unfortunately, I use 5.7 version and looks like `regexp_substr` doesn't exist there. – feyen01 Jan 14 '21 at 14:36
@feyen01 . . . That makes the problem much harder. – Gordon Linoff Jan 14 '21 at 21:49

score 0 · Answer 2 · answered Jan 16 '21 at 16:58

you could add a custom function like this:

DELIMITER //
CREATE FUNCTION firstNumber(s TEXT)
    RETURNS INTEGER
    COMMENT 'Returns the first integer found in a string'
    DETERMINISTIC
    BEGIN

    DECLARE token TEXT DEFAULT '';
    DECLARE len INTEGER DEFAULT 0;
    DECLARE ind INTEGER DEFAULT 0;
    DECLARE thisChar CHAR(1) DEFAULT ' ';

    SET len = CHAR_LENGTH(s);
    SET ind = 1;
    WHILE ind <= len DO
        SET thisChar = SUBSTRING(s, ind, 1);
        IF (ORD(thisChar) >= 48 AND ORD(thisChar) <= 57) THEN
            SET token = CONCAT(token, thisChar);
        ELSEIF token <> '' THEN
            SET ind = len + 1;
        END IF;
        SET ind = ind + 1;
    END WHILE;

    IF token = '' THEN
        RETURN 0;
    END IF;

    RETURN token;

    END //
DELIMITER ;

source

After you have to execute query in this way

SELECT * FROM models ORDER BY firstNumber(name)

(be aware that this sorting is not indexed)

MySQL order by first word in string

2 Answers2