Ordering varchar column numerically

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • blue

    Ordering varchar column numerically

    Is there a preferred way to order a varchar column numerically (for those
    that are numeric), then alphanumericall y for all others?

    I've tried:

    ORDER BY CASE WHEN IsNumeric(<colu mn_name>) = 1 THEN CONVERT(Float,
    <column_name> ) ELSE 999999999 END;

    and

    ORDER BY CASE WHEN IsNumeric(<colu mn_name>) = 1 THEN 0 ELSE 1 END, CASE WHEN
    IsNumeric(<colu mn_name>) = 1 THEN CONVERT(Float, <column_name> ) ELSE 999999
    END, <column_name> ;

    Neither of these however give the desired results.

    Any ideas?

    Thanks,
    Frank


  • Simon Hayes

    #2
    Re: Ordering varchar column numerically

    It would be useful to see some sample data - are your 'numeric' data
    integers, decimals, or floats; are there negative numbers in the data,
    etc.? Assuming that your data is made up of either positive integers or
    strings, then this should work:

    select *
    from dbo.MyTable
    order by case when MyColumn like '%[^0-9]%' then 1 else 0 end, MyColumn

    ISNUMERIC() might work, but it considers so many things to be numbers
    that you may not get the results you want:

    select *
    from dbo.MyTable
    order by isnumeric(MyCol umn), col1

    If this doesn't help, then please post DDL and sample data:



    Simon

    Comment

    • David Portas

      #3
      Re: Ordering varchar column numerically

      Assuming your numerics are just positive integers, try:

      SELECT col
      FROM T1
      ORDER BY
      CASE WHEN col LIKE '%[^0-9]%'
      THEN 9E99
      ELSE CAST(col AS INTEGER) END
      , col

      --
      David Portas
      SQL Server MVP
      --

      Comment

      • blue

        #4
        Re: Ordering varchar column numerically


        "Simon Hayes" <sql@hayes.ch > wrote in message
        news:1107442937 .220047.113030@ o13g2000cwo.goo glegroups.com.. .[color=blue]
        > It would be useful to see some sample data - are your 'numeric' data
        > integers, decimals, or floats; are there negative numbers in the data,
        > etc.? Assuming that your data is made up of either positive integers or
        > strings, then this should work:
        >
        > select *
        > from dbo.MyTable
        > order by case when MyColumn like '%[^0-9]%' then 1 else 0 end, MyColumn[/color]

        Thank you Simon, the above syntax worked like a charm!

        Frank
        [color=blue]
        > ISNUMERIC() might work, but it considers so many things to be numbers
        > that you may not get the results you want:
        >
        > select *
        > from dbo.MyTable
        > order by isnumeric(MyCol umn), col1
        >
        > If this doesn't help, then please post DDL and sample data:
        >
        > http://www.aspfaq.com/etiquette.asp?id=5006
        >
        > Simon
        >[/color]


        Comment

        • blue

          #5
          Re: Ordering varchar column numerically


          "David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
          news:1107443475 .536452.235950@ f14g2000cwb.goo glegroups.com.. .[color=blue]
          > Assuming your numerics are just positive integers, try:
          >
          > SELECT col
          > FROM T1
          > ORDER BY
          > CASE WHEN col LIKE '%[^0-9]%'
          > THEN 9E99
          > ELSE CAST(col AS INTEGER) END
          > , col
          >
          > --
          > David Portas
          > SQL Server MVP
          > --
          >[/color]

          Thanks David, this one actually works better. If I use:

          order by case when <mycolumn> like '%[^0-9]%' then 1 else 0 end, <mycolumn>

          It does not correctly order the integers (i.e. 1,10,11,12,2,3, 4,...), but
          does correctly order the alphnumerics. Using your suggestion, it works for
          both cases.

          Frank


          Comment

          Working...