Last post Jan 24, 2019 09:31 PM by PatriceSc
Jan 23, 2019 08:54 AM|Baiju EP|LINK
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
i want as under
Jan 24, 2019 12:39 AM|KathyW|LINK
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.)
Jan 24, 2019 02:30 AM|Wei Zhang|LINK
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
Jan 24, 2019 09:31 PM|PatriceSc|LINK
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.