Martijn Tonies wrote:
>Hi,
>
>==
>I have an ID field in my database...it reads like this
>
>ASS1
>ASS23
>ASS4
>ASS10
>ASS6
>when i'm retrieving the data by taking ORDER BY clause it is sorting like
>this
>
>ASS1
>ASS10
>ASS23
>ASS4
>ASS6
>
>means its only sorting by the 4 the character. i want the sorting to be done
>like the following
>==
>
>No, it means it's sorting by alphabet, not by the 4th character.
>
>ASS10 comes after ASS1, makes perfect sense.
>
>==
>ASS1
>ASS4
>ASS6
>ASS10
>ASS23
>
>Solutions are greatly appreciated
>==
>What you want, is that the sorting acts like to ignore "ASS" and
>use the number behind it as an integer.
>
>Perhaps you can cut off the first 3 characters, cast the rest to
>an Integer and order by that?
>
>
>
SELECT *
FROM tablename
ORDER BY ((SUBSTRING(columnToSort FROM 4)) + 0) as numberpart
