Mysql Sort Split Character String

I have the following table, say, MyDRAW

DRAWNO   PRIZENO
A123/14  8458

I managed to use SUBSTRING function to spli the PRIZENO into 4 character, 8 4 5 8

SUBSTRING(PRIZENO, 1, 1) AS FIRSTNo,
SUBSTRING(PRIZENO, 2, 1) AS SECONDNo,
SUBSTRING(PRIZENO, 3, 1) AS THIRDNo,
SUBSTRING(PRIZENO, 2, 1) AS FOURTHNo,

But, My Question is how to sort this number into their order ASC, for example: 4 5 8 8

Therefore, after the sorting, my Select result can be as follows:


DRAWNO  PRIZENO FIRSTNo SECONDNo THIRDNo FOURTHNO SORTED1 SORTED2 SORTED3 SORTED4
A123/14 8458    8       4        5       8        4       5       8       8

Thanks.

No Choice, and I am not sure whether there are any good / better way …

I did like this …

CONCAT(
IF(SUBSTRING(PRIZENO, 1, 1)=0,“0”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=0,“0”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=0,“0”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=0,“0”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=1,“1”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=1,“1”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=1,“1”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=1,“1”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=2,“2”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=2,“2”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=2,“2”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=2,“2”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=3,“3”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=3,“3”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=3,“3”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=3,“3”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=4,“4”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=4,“4”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=4,“4”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=4,“4”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=5,“5”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=5,“5”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=5,“5”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=5,“5”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=6,“6”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=6,“6”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=6,“6”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=6,“6”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=7,“7”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=7,“7”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=7,“7”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=7,“7”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=8,“8”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=8,“8”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=8,“8”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=8,“8”,“”),
IF(SUBSTRING(PRIZENO, 1, 1)=9,“9”,“”),
IF(SUBSTRING(PRIZENO, 2, 1)=9,“9”,“”),
IF(SUBSTRING(PRIZENO, 3, 1)=9,“9”,“”),
IF(SUBSTRING(PRIZENO, 4, 1)=9,“9”,“”)) AS CON1,

you could redesign the table so that it conforms to first normal form

:slight_smile: