mysqlgroup-byzerofill

how to mysql group by zerofill?

SELECT count(w.c1) AS count
     , w.c1 AS color
FROM
  data w
GROUP BY
  w.c1
ORDER BY
  w.id DESC
LIMIT
  50000;

I'm wondering, is there any kind of mysql query to group by zerofill values, i have all values in c1 as rgb code '0002500034' (r=000/g=250/b=034), after query it shows like 000250034->250034

Solution

  • What about the LPAD MySQL function ? LPAD(str, len, padstr) 

    SELECT count(w.c1) AS count
 , LPAD(w.c1, 9, '0')  AS color
FROM
  data w
GROUP BY
  w.c1
ORDER BY
  w.id DESC
LIMIT
  50000;

    You can also turn your column type into CHAR(9).