SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Feb 2014
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql Sort Split Character String

    I have the following table, say, MyDRAW

    Code:
    DRAWNO   PRIZENO
    A123/14  8458
    I managed to use SUBSTRING function to spli the PRIZENO into 4 character, 8 4 5 8

    Code:
    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:
    Code:
    DRAWNO  PRIZENO FIRSTNo SECONDNo THIRDNo FOURTHNO SORTED1 SORTED2 SORTED3 SORTED4
    A123/14 8458    8       4        5       8        4       5       8       8
    Thanks.

  2. #2
    SitePoint Member
    Join Date
    Feb 2014
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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,

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cybervista View Post
    ... I am not sure whether there are any good / better way ...
    you could redesign the table so that it conforms to first normal form

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •