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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply