0

I have a field that is stored as a string in my db and is commonly an alphanumeric value. Ex: '11', '11a', '12'. I was having a sorting issue because MySQL was sorting '11' before '2' since they are strings. using an order by statement with table.columnName + 0 has mostly solved my problem, though entries beginning with letters are still a bit unexpected in how they are sorted. For example, 'z10' comes before 'AB110'. I've seen in other questions asked that MySQL doesn't support alphanumeric sorting, which would explain this behavior. I would really like to figure out if I can have an entry of '11a' to come after '11' and before '12'. Is this possible? I've also tried ORDER BY CAST(columnName AS UNSIGNED INTEGER) but wasn't having any luck.

slime_saw
  • 47
  • 6
  • In Ruby one could write the following. `arr = ['11', '11b', '12', '5b', '11a', '100']; sz = arr.max_by(&:size).size + 1; arr.sort_by do |s|; t = s.match?(/\A\d+\z/) ? (s + ' ') : s; t.rjust(sz); end #=> ["5b", "11", "11a", "11b", "12", "100"]`. Note `' '.ord => 32 < 'A'.ord => 65 < 'a'.ord => 97`. Is that any help? – Cary Swoveland Jan 30 '23 at 20:58
  • In the above note that `sz #=> 4` and `arr.map do |s|; t = s.match?(/\A\d+\z/) ? (s + ' ') : s; t.rjust(sz); end #=> [" 11 ", " 11b", " 12 ", " 5b", " 11a", "100 "]`. – Cary Swoveland Jan 30 '23 at 21:03
  • You could try something dirty like `ORDER BY IFNULL(NULLIF(CAST(columnName AS UNSIGNED), 0), 4294967295), columnName` – user1191247 Jan 31 '23 at 00:26
  • @CarySwoveland thanks for your answer. this is useful for me understanding ruby logic for sorting. this was my initial approach (just using ruby to sort an array of values) to nail down logic. it's the mysql part that's been difficult for me – slime_saw Jan 31 '23 at 15:27
  • 1
    @nnichols i've started moving in a direction similar to this. I found [this](https://stackoverflow.com/a/8557307/19944351) answer which has suggestions similar to your approach in casting values – slime_saw Jan 31 '23 at 15:28

0 Answers0