Re: SQL Text and Number sort

I have a field I am sorting on that is a text field which includes
numbers.
Is there any way to sort this field so that, for example, 2356 comes
before
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


.

Relevant Pages

  • Derived RichTextBox containing objects?
    ... ArrayList as well as the Text & Rtf properties. ... Text: SELECT field1 FROM table1 ... FYI x.ToStringoutputs 'field1' and y.ToString ... but I wanted the Editor to be very easy to use. ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Re: indexing problem
    ... The problem is a secondary field is also required which is a character field. ... Let's say field1 is the numeric field and field2 is the character field. ... INDEX ON STR+field2 TAG myindex ...
    (microsoft.public.fox.programmer.exchange)
  • RE: binding a combo box to with 2 lookup columns to two source col
    ... Control source: field1 (table1) ...
    (microsoft.public.access.formscoding)
  • Re: Howto embed select statements into other select statements
    ... I want to calculate the fraction of two counts and I have created the ... by id) table1, ...
    (comp.databases)
  • Re: Report Filter
    ... "Field1" that I want to filter out null entries. ... To filer out null entries from this field in the report shouldn't ... SELECT * FROM Table1 WHERE Field1 Is Not Null ...
    (microsoft.public.access.reports)