Re: SQL Text and Number sort
- From: "Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom>
- Date: Tue, 4 Apr 2006 17:51:39 +0100
I have a field I am sorting on that is a text field which includesnumbers.
Is there any way to sort this field so that, for example, 2356 comesbefore
12685 while maintaining alphabetical order for the true text entries?
I can do it using SQL Server using a hack
SELECT field1
FROM table1
ORDER BY
CASE ISNUMERIC(field1)
WHEN 0 THEN field1
WHEN 1 THEN RIGHT('0000' + LTRIM(RTRIM(field1)), 4)
END
field1 is a 4 character field.
I am distinguishing numeric from non-numeric and adding leading 0's to the
numeric so that is sorts properly.
Stephen Howe
.
- Prev by Date: Is it possible to repaire damaged MDAC/ADO on XP with SP2 ?
- Next by Date: Re: SQL Text and Number sort
- Previous by thread: Is it possible to repaire damaged MDAC/ADO on XP with SP2 ?
- Next by thread: Re: SQL Text and Number sort
- Index(es):
Relevant Pages
|
|