CODEWITHSUNDEEP
mysql
Giuseppe
Giuseppe

Reputation: 25

How to Order Name without Prefix Name in MySQL?

I have table like this. I want to return customers_name and sort customers_name column by ascending (A to Z), however the prefix name should not be part of the order. Example: Ir. Finna Nugraha must be above Heidi Gohms.

ms_customer
Field
no.
customers_id
customers_name
address
customers_name
August Neight
Denny Smith, Dr.
Eva Novianta, S.H.
Heidi Gohms
Ir. Finna Nugraha
Irquan Abdullah
John Haambt
Maria Sirnat
Tommy Santos
Ugame Konogawa

Upvotes: 0

Views: 147

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520988

You could use REGEXP_REPLACE to remove the leading prefix while sorting:

SELECT *
FROM ms_customer
ORDER BY REGEXP_REPLACE(customers_name, '^\w+\.\s+', '');

If you are using a version of MySQL earlier than 8+, then we can try the following workaround:

SELECT *
FROM ms_customer
ORDER BY
    CASE WHEN customers_name REGEXP '^\w+\.\s+'
         THEN SUBSTRING(customers_name, INSTR(customers_name, ' ') + 1)
         ELSE customers_name END;

Upvotes: 3

Related Questions