Sorting a VARCHAR field in MySQL

问题: I have a MySQL table with just one column. There are over 16 million rows in this table. The table contains all the RGB color values closed by parentheses. For instance, (1...

问题:

I have a MySQL table with just one column. There are over 16 million rows in this table. The table contains all the RGB color values closed by parentheses. For instance, (100,155,255). However, they are unsorted. How do I sort such that the first row will be (0,0,0), the second row (0,0,1) etc and the last row be (255,255,255)? I have tried ORDER BY CAST which doesn't seem to work.


回答1:

You could try using cast on substring

SELECT your_column
from your_table 
order by CAST(replace( SUBSTRING_INDEX(SUBSTRING_INDEX( your_column, ',', 1), ',', -1) , '(','') AS UNSIGNED) 
   , CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( your_column, ',', 2), ',', -1) AS UNSIGNED) 
   , CAST(replace(SUBSTRING_INDEX(SUBSTRING_INDEX( your_column, ',', 3), ',', -1), ')','') AS UNSIGNED) 
  • 发表于 2019-02-19 18:14
  • 阅读 ( 141 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除