CODEWITHSUNDEEP
mysqlmariadb
Robert
Robert

Reputation: 508

MySQL column of alphanumeric heterogeneous strings with one number inside, sort by that number

Sorry for the long title

PLEASE NOTE: this should be done with MariaDB 10.4.x

PLEASE NOTE: I have tried plenty of answers/queries here on stackoverflow, so I think it makes no sense to copy and paste at least a dozen of queries.

I have a column that is a "subject" columns

so it is VARCHAR and could have any string in it

many "subjects" though will have a number inside "some AA text 345 other text" but also "some BB quite text 2563 again text"

Is it possible to make a query that

  1. will extract only the subjects that actually contains a number (that number can be one digit , two digit, three digit and four digit ) , mean omit those with letters or others chars but no digits
  2. really naturally sort the subjects with numbers

mean that the two subjects above will be extracted and sorted this way

"some at text 345 other text"

"some big text with 1250 other text"

"some at eagle quite text 2563 again text"

"some big go for it text 3343 again text"

for the simple reason that 345 < 1250 < 2563 < 3343

I tried so many solutions but often the result is "wrong" because you get this sorting

some at ...
some at ...
some big ...
some big ...

mean they are sorted as first the alphabetical orders

while others queries looks not to consider ASC or DESC

while I just need the sorting by the numbers ( when numbers are present ) ONLY :-)

Thank you for any hint

Upvotes: 1

Views: 81

Answers (1)

Matthias Gwiozda
Matthias Gwiozda

Reputation: 595

With the following table:

CREATE TABLE `testtable` (
  `subject` varchar(45) NOT NULL,
  PRIMARY KEY (`subject`)
) ENGINE=InnoDB;

You can use this query:

SELECT 
subject,
CAST(regexp_substr(subject, '[0-9]+') AS UNSIGNED)
    FROM testtable
        --  only the subjects that actually contains a number 
        WHERE regexp_substr(subject, '[0-9]+')
            -- really naturally sort the subjects with numbers
            ORDER BY CAST(regexp_substr(subject, '[0-9]+') AS UNSIGNED);

Upvotes: 1

Related Questions