There is such a query SELECT text FROM table_test WHERE text LIKE '%5%' GROUP BY text .

Result of performance: `

 +------------+ | text | +------------+ | AS.9.5 | | AS.8.5 | | AS.7.5 | | AS.6.5 | | AS.5.5 | | AS.5.4 | | AS.5.3 | | AS.5.2 | | AS.5.1 | | AS.3.5 | | ASK.29.5 | | ASK.29.25 | | ASK.29.15 | | AST.28.5 | | AST.28.25 | | AST.28.15 | | AS.20.5 | | AS.2.5 | | ASDA.19.5 | | AB-B-5.1 | | AB-B-5.3 | | AB-B-5.4 | | AB-B-13.5 | | AB-B-15.8 |

How to make so that when you enter the number 5, first output data that starts with 5? And after that, in the same query, the data was output, starting with the smallest value and to the last. For example:

 +------------+ | text | +------------+ | AS.5.1 | | AS.5.2 | | AS.5.3 | | AS.5.4 | | AS.5.5 | | AB-B-5.1 | | AB-B-5.3 | | AB-B-5.4 | | AS.2.5 | | AS.3.5 | | AS.7.5 | | AS.8.5 | | AS.9.5 | | AB-B-13.5 | | AB-B-15.8 | | ASK.20.5 | | ASK.28.5 | | ASK.29.15 |
  • This is still not a strict description of the format ... There is a left part and a right (number). Can there be anything on the left side other than letters, signs "-" and "."? Is there a fractional part on the right side? - pegoopik
  • How to be, for example, with such values: ASK1 ; AKS.0 , AS101-0.1 ; 102 ; - pegoopik
  • @pegoopik Options can be AS.2, AS.2.2, AS.12.2. The same if in the left part there are 3 letters. Also, this option is present. ASK.-2.1, AS-SK-.2.2, AS-A.-2.2 - Pavel

1 answer 1

Provided that you have the format string "XXX.number", where XXX is a certain character set that does not contain the character ".", You can sort it like this:

 SELECT text FROM table_test WHERE text LIKE '%5%' GROUP BY text ORDER BY --сначала числа, которые начинаются на 5. IF(SUBSTR(text, LOCATE('.', text) + 1) LIKE '5%', 0, 1), --затем по возрастанию чисел. CAST(SUBSTR(text, LOCATE('.', text) + 1) AS DECIMAL(20, 6))

UPD: I did not understand all your limitations. As a result, I implemented the following algorithm:

 Анализируем строку справа на лево. Считаем, что перед числом обязательно есть разделитель("-" или "."). Проверяем что после разделителя находятся только символы "0-9" и ".". Если данные некорректные - получим в качестве правой части пустую строку. Подробно описывать не буду. просто посмотрите результат запроса:

request:

 SELECT * FROM( SELECT T.*, CAST( CASE WHEN right_part_dif NOT RLIKE '[^0-9.]' THEN right_part_dif WHEN right_part_dot_2 NOT RLIKE '[^0-9.]' THEN right_part_dot_2 WHEN right_part_dot_1 NOT RLIKE '[^0-9.]' THEN right_part_dot_1 ELSE '' END AS DECIMAL(20, 6)) right_part FROM( SELECT text, SUBSTRING_INDEX(text, '-', -1)right_part_dif, SUBSTRING_INDEX(text, '.', -1)right_part_dot_1, SUBSTRING_INDEX(text, '.', -2)right_part_dot_2 FROM( SELECT 'ASK.53.05' text UNION ALL SELECT '102' UNION ALL SELECT 'A1.104' UNION ALL SELECT 'A123' UNION ALL SELECT '123A' UNION ALL SELECT 'AKS-1/-0' UNION ALL SELECT 'ASK.123' UNION ALL SELECT 'ASK-123-1253' UNION ALL SELECT 'ASK.123-1253.123' )T)T)T ORDER BY IF(text LIKE '%5%', 0, 1), right_part

result:

 text right_part_dif right_part_dot_1 right_part_dot_2 right_part ASK.53.05 ASK.53.05 05 53.05 53.050000 ASK-123-1253 1253 ASK-123-1253 ASK-123-1253 1253.000000 ASK.123-1253.123 1253.123 123 123-1253.123 1253.123000 123A 123A 123A 123A 0.000000 AKS-1/-0 0 AKS-1/-0 AKS-1/-0 0.000000 A123 A123 A123 A123 0.000000 102 102 102 102 102.000000 A1.104 A1.104 104 A1.104 104.000000 ASK.123 ASK.123 123 ASK.123 123.000000

Perhaps this query solves a more general problem. If necessary, you can simplify. But at least on the data from the example in question, he behaves correctly.

  • But does CAST (text AS FLOAT) list the string of the type AST.28.15 ? - Grundy
  • one
    @Grundy, inattentively looked at the data in question, corrected. - pegoopik
  • It seems to me that I, too, need to be corrected with IF , the line begins with letters, it means text LIKE '5%' - always false - Grundy
  • @Grundy, already). - pegoopik
  • @pegoopik Thanks for the reply! But there is an error # 1054 - Unknown column 'S' in 'order clause' - Pavel