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
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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