Hello. There are lines in which there can be letters as well as numbers. How to sort them only by numeric value or by letter without numbers? I read that there are functions DEPTNO and ENAME, but they do not work in MYSQL. How can I create their implementation in MYSQL?

  • Explain. The strings "a1b2c3" and "d4e5f6" should be sorted: a) with alphabetic comparison as "abc" and "def", 2) with digital - like numbers 123 and 456? Or so that when alphabetic comparability is taken into account only a part to the numbers, and when the number is digitized, a part to the letters is allocated? - user6550
  • option A is needed - zloctb
  • Sort after sampling. Because a similar replacement in MySQL looks like this (and this is only deleting 1,2,3): SELECT s3 FROM (SELECT REPLACE (s2, '3', '') as s3 FROM (SELECT REPLACE (s1, '2', '') as s2 FROM (SELECT REPLACE ( string , '1', '') as s1 FROM table ) as s1) as s2) as s3 - user6550
  • one
    @klopp, why so long. Total 10 replays: SELECT *, REPLACE (REPLACE (REPLACE ( string , '1', ''), '2', ''), '3', '') ... <etc.> as c1 FROM table order by c1 - msi
  • But what the difference, it's still a perversion. Like the problem itself :) - user6550


1 answer 1

Try converting data types. Of course, you should take into account what values ​​you have in the field being sorted, but sometimes it is enough to do the following:

 SELECT `field_name` FROM `table_name` ORDER BY CAST( `field_name` AS SIGNED ) // или же просто SELECT `field_name` FROM `table_name` ORDER BY `field_name` + 0