Select data from DB sorted as per EmpNo

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

Answer 1

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.)

Answer 2

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

Answer 3

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.