Professional Web Applications Themes

Sorting VarChar in ascending order - Microsoft SQL / MS SQL Server

Hi, Good Day! In my SQL database, I define my ClientCode as varchar. For eg: A12, 3456, 987, B19 I faced problem in sorting those records in order. Wrong sorting: 3456, 987, A12, B19 Correct sorting: 987, 3456, A12, B19 What shd i write to convert only those varchar that are in numbers so that there are in order. Can someone help? Thanks in advanced. rgds, Phoebe....

  1. #1

    Default Sorting VarChar in ascending order

    Hi, Good Day!

    In my SQL database, I define my ClientCode as varchar. For eg: A12, 3456,
    987, B19
    I faced problem in sorting those records in order.
    Wrong sorting: 3456, 987, A12, B19
    Correct sorting: 987, 3456, A12, B19

    What shd i write to convert only those varchar that are in numbers so that
    there are in order.

    Can someone help?
    Thanks in advanced.

    rgds,
    Phoebe.


    Phoebe. Guest

  2. #2

    Default Re: Sorting VarChar in ascending order

    Try using conditional order clause.

    create table test
    (field varchar(100))

    insert into test
    select '3456'
    union
    select '987'
    union
    select 'A12'
    union
    select 'B19'

    select
    *
    from test
    order by case when IsNumeric(field) = 1 then te('0', 100 - len(field)) + field
    else field
    end

    drop table test

    --
    Dean Savovic
    www.teched.hr


    "Phoebe." <com> wrote in message news:phx.gbl... 


    Dean Guest

  3. #3

    Default Re: Sorting VarChar in ascending order

    CREATE TABLE phoebe (client_code varchar (10))
    GO
    INSERT INTO phoebe VALUES('987')
    INSERT INTO phoebe VALUES('3456')
    INSERT INTO phoebe VALUES('A12')
    INSERT INTO phoebe VALUES('B19')

    SELECT * FROM phoebe
    ORDER BY CASE WHEN ISNUMERIC(client_code) = 0 THEN client_code END,

    CASE WHEN ISNUMERIC(client_code) = 1 THEN CAST(client_code AS
    INT) END

    DROP TABLE phoebe


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Phoebe." <com> wrote in message
    news:phx.gbl... 


    Jacco Guest

Similar Threads

  1. Replies: 0
    Last Post: July 17th, 11:00 PM
  2. Order By or sorting nested querys
    By ICI-MASA in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 8th, 03:39 PM
  3. Sorting by alphabetical order
    By Gabriel_Ayala@adobeforums.com in forum Adobe Indesign Windows
    Replies: 6
    Last Post: August 17th, 09:08 PM
  4. Replies: 0
    Last Post: October 6th, 04:52 PM
  5. Records in Ascending Order
    By Kevin Spencer in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 27th, 10:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139