November 4, 2004 at 8:18 pm
how to sort a char(5) column with domain... {1,1a,1b,1c,2,2a,2b,3,4,5,5a,5b,6,8,11,12,12b,12c......} ????
November 4, 2004 at 8:36 pm
Hi John, your message is a bit terse. Please provide sample data and how you would like it to be sorted.
Cheers
Phil
November 5, 2004 at 6:13 am
I assume you mean have it sort
{1,1a,1b,1c,2,2a,2b,3,4,5,5a,5b,6,8,11,12,12b,12c...}
Instead of the norm
{1,11,12,12b,12c,1a,1b,1c,2,2a,2b,3,4,5,5a,5b,6,8...}
The only way to get in numeric order would be to strip the alphas and cast as numeric in the order by clause. Which means you would need to build a UDF to take the char(5) and return a numeric, inside have it remove all characters not betwen 0 and 9. Then use your UDF in the ORDER BY clause.
November 5, 2004 at 6:49 am
select [col]
from
order by cast(
(case when patindex('%[a-z]%',[col]) = 0
then [col]
else left([col],patindex('%[a-z]%',[col])-1)
end) as int),[col]
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply