Re: SQL Text and Number sort

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: DSUM HELP!!!
    ... Or even more efficient would be to use a subquery in the FROM clause of your query and join on Field1. ... FROM Table1 INNER JOIN ... GROUP BY Field1) as Temp ... You can only do the subquery in the FROM clause if your field and table names following the naming convention. ...
    (microsoft.public.access.gettingstarted)
  • 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)