Столбец сортировки MYSQL с правильным номером в середине предложения

#mysql

Вопрос:

У меня есть столбец в базе данных MySQL с именем title , который имеет тип данных varchar (255). При сортировке по title ASC строки сортируются в этом порядке:

 Example - A1 Some description
Example - A2 A different description
Example - A20 Some other wordin&
Example - A21 Different text
Example - A3 Some text
Example - A4 Some final text
Example item that does not follow pattern

В то время как мне нужно, чтобы он был отсортирован следующим образом:

 Example - A1 Some description
Example - A2 A different description
Example - A3 Some text
Example - A4 Some final text
Example - A20 Some other wordin&
Example - A21 Different text
Example item that does not follow pattern

Я пытался использовать этот запрос:

 SELECT *,
   CAST(title AS UNSIGNED) casted_title
FROM example_table
ORDER BY casted_title,
         title

Но это не помогло, так как casted_title все строки возвращаются пустыми.

Я также пробовал этот запрос:

 SELECT *,
   BIN(title) AS bintitle
FROM cycle_tracker_event_cate&ories
ORDER BY bintitle,
         title

Это тоже не помогло. Все строки возвращают bintitle значение 0.

Есть ли способ выполнить сортировку желаемым образом, учитывая, что рассматриваемые числа относятся к середине предложения и могут быть рядом с буквой, а могут и не быть?

Редактировать:

Не все элементы в списке следуют одному и тому же шаблону, начинающемуся с «Example — «. Некоторые из них вообще не содержат никаких чисел, я просто сосредоточился на приведенных выше, потому что именно с ними у меня возникли проблемы.

1. Всегда ли существует - , за которым следует пробел, за которым следует символ типа A , а затем число? Если да, то хотите ли вы, чтобы строки сортировались только по этому номеру и ни по чему другому?

2. Некоторые строки вообще не имеют номера. Извините, я должен был упомянуть это в своем вопросе. Сейчас я его обновлю. Спасибо.

3. Преобразование строки в число всегда возвращает результат 0 , если оно не начинается с числа. Он не будет искать номер в середине.

4. Если в нем есть номер, всегда ли он после Example - A ?

5. Есть ли какой-нибудь способ добавить в таблицу новый столбец с номером, по которому вы хотите упорядочить? Даже если вы сможете сделать это таким образом, это будет очень неэффективно.

Ответ №1:

Вам нужно получить подстроку после Example - A префикса, прежде чем преобразовывать ее в число, по которому вы можете упорядочивать. В противном случае он просто преобразуется в 0 .

 ORDER BY IF(title LIKE 'Example - A%', 0 SUBSTR(title, LENGTH('Example - A')), 99999)

1. К сожалению, это не сработало бы, поскольку не существует предсказуемого шаблона. Мои извинения, я думал, что ясно дал это понять в вопросе. Похоже, мой единственный вариант — следовать вашему комментарию и добавлять дополнительный столбец и сортировать по нему вместо этого. Спасибо.