Sorting MySQL text Column with numbers

Question

I have stored these texts in MySQL table column like this

    Hello, Your Order Id is 3458
    Sir, Your Order Id is 4833 
    With reference to the booking, your order Id is 4324

How can I sort this column according to Order IDs ?

Tim Biegeleisen · Accepted Answer · 2020-06-29 05:59:28Z

2

Assuming the order ID would always be the last word in the column string, you could try:

SELECT *
FROM yourTable
ORDER BY CAST(SUBSTRING_INDEX(col, ' ', -1) AS UNSIGNED);

If the order could appear anywhere, then it gets more tricky. If you are using MySQL 8+, then REGEXP_REPLACE comes in handy:

SELECT *
FROM yourTable
ORDER BY
    CAST(REGEXP_REPLACE(col, '^.*(?i)your order id is ([0-9]+).*', '$1') AS UNSIGNED);

Demo

answered Jun 29, 2020 at 5:49

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.