locked
Select data from DB sorted as per EmpNo RRS feed

  • Question

  • User810354248 posted

    In my asp.net+vb+sql web i have a grid view in which i am using this code to collect data.

    SELECT * FROM [Images] ORDER BY EMPNO ASC

    the field EMPNO is a varchar(50) field

    data will be like this CCX125, CCX199, CCX200,CCX1080

    the above code picks data but it picks like this

    CCX1080

    CCX125

    CCX199

    CCX200

    i want as under

    CCX125

    CCX199

    CCX200

    CCX1080

    Wednesday, January 23, 2019 8:54 AM

Answers

  • User-1174608757 posted

    Hi Baiju EP,

    According to your description, I have made  a sample here. I suggest you to using PATINDEX() function like below :

    SELECT * FROM TEST ORDER BY CAST(SUBSTRING(EMPNO + '0', PATINDEX('%[0-9]%', EMPNO + '0'), LEN(EMPNO + '0')) AS INT)

    Here it shows 

    Best Regards

    Wei Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 24, 2019 2:30 AM

All replies

  • User409696431 posted

    You want it ordered as if they are numbers, but they are not.  Varchar sorts alphabetically, not numerically.  That means that CCX10... comes before CCX12...

    You can refer to https://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/ for an example of parsing the values and splitting them, and sorting them separately.  If the values always have three alphabetic characters in the front, it's doable.  (If they are always CCX, you can make it simpler.)

    Thursday, January 24, 2019 12:39 AM
  • User-1174608757 posted

    Hi Baiju EP,

    According to your description, I have made  a sample here. I suggest you to using PATINDEX() function like below :

    SELECT * FROM TEST ORDER BY CAST(SUBSTRING(EMPNO + '0', PATINDEX('%[0-9]%', EMPNO + '0'), LEN(EMPNO + '0')) AS INT)

    Here it shows 

    Best Regards

    Wei Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 24, 2019 2:30 AM
  • User753101303 posted

    Hi,

    Another option would be to consider fixing those values ie using CCX0125, CCX0199, CCX0200 and CCX1080 which sorts fine...

    Then my next option would be to store the prefix and the number in two separate columns so that it sorts fine (and possibly a compute column if needed).

    Spliting a value found in a single column for ordering (or more generally processing separately its components) would be really my very last choice. It means basically that you are stored two or more atomic values in a single column.

    Thursday, January 24, 2019 9:31 PM