I'm trying to query and sort a column without much luck. My data looks like this and is consistent with the preceding "WP".
Should display as
WP1-WP2
WP2-WP3
WP10-WP11
WP10-WP12
Actually displays as
WP1-WP2
WP10-WP11
WP10-WP12
WP2-WP3
I've attempted adapting several answers found on here with no success.
Question is how do I sort this list as it should??
EDIT: I am using distinct in the query, not sure if this will effect the problem I'm having
Try this,
select *
from myStringSorting
order by Cast(Replace(Substring(mycol,0,CHARINDEX('-',mycol)),'WP','') as Int) asc
,Cast(Replace(Substring(mycol,CHARINDEX('-',mycol) + 1,LEN(mycol)),'WP','') as Int) asc
Check out this link, http://sqlfiddle.com/#!3/ca45f/5
With DISTINCT,
With CTE as
(
select distinct mycol
from myStringSorting
)
select * from CTE
order by Cast(Replace(Substring(mycol,0,CHARINDEX('-',mycol)),'WP','') as Int) asc
,Cast(Replace(Substring(mycol,CHARINDEX('-',mycol) + 1,LEN(mycol)),'WP','') as Int) asc
With DISTINCT, without CTE, using sub query,
Select a.mycol from
(
select distinct mycol
from myStringSorting
)as a
order by Cast(Replace(Substring(a.mycol,0,CHARINDEX('-',a.mycol)),'WP','') as Int) asc
,Cast(Replace(Substring(a.mycol,CHARINDEX('-',a.mycol) + 1,LEN(a.mycol)),'WP','') as Int) asc
Another method
select * from myStringSorting
order by len(mycol),mycol
