SQL ignore parenthesis in name field for ORDER BY-CodePudding

I would like rows returned in a MySQL query to be sorted alphabetically by surname for which I have an SQL query like:

SELECT 
  id, 
  substring_index(name, ' ', -1) as surname
FROM
  my_table
ORDER BY 
  surname asc

However, some names have parenthesis to denote some special circumstance such as: Laura Angel (retired)

How can I modify my SQL above to ignore the parenthesised text, to sort by surname alphabetically?

CodePudding user response：

Try with nested replaces to remove the parentheses.

SELECT 
  id, 
  substring_index(name, ' ', -1) as surname
ORDER BY 
REPLACE( REPLACE( surname  , '(' , '') , ')' , '') ASC;

Test and modify according to you version of SQL. Not tested.

CodePudding user response：

You can use this solution:

SELECT id, 
  substring_index(rtrim(substring_index(name, '(', 1)), ' ', -1) as surname 
FROM test.test
ORDER BY 
  surname asc;