locked
Ordering by alphanumeric characters RRS feed

  • Question

  • Hi i am currently doing a project in which the database needs to sort the lot numbers
    prefix is nvarchar
    lotnum is int
    suffix is nvarchar

    i have managed to convert the lot number
    code i used is
    Select (case when prefix is null then '' else prefix end) +
    CONVERT ( nvarchar , ( lotnumber ) ) +(case when suffix is null then '' else suffix end)
    (values in the database are a1a,1a,1,2,100)
    when i order by lotnumber i get
    a1a
    1a
    1
    2
    100

    then prefix to the order by
    and get this result
    1
    a1a
    1a
    2
    100

    i have added the suffix as well and returns the same result

    i need to order it as follows

    1
    1a
    2
    100
    a1a

    Please could someone help me on this

    Regards Ismail

    Friday, July 27, 2012 9:32 AM

Answers

  • Hi,

    See if this helps

    --Sample Data ( added few more rows for test)
    declare @t table
    ( 
    prefix  nvarchar(1)
    , lotnumber int
    , suffix nvarchar(1)
    )
    
    insert into @t
    select 'a',1,'a'
    union all
    select NULL,1,'a'
    union all
    select NULL,1, NULL
    union all
    select NULL,1, 'c'
    union all
    select NULL,2,NULL
    union all
    select NULL,'100',NULL
    union all
    select 'a','1','b'
    
    --Actual Query 
    declare @maxlotnumber decimal(19,2)
    select @maxlotnumber = 2147483647.0 --max integer value possible
    
    select case when prefix is null then '' else prefix end 
            +  CONVERT ( nvarchar(10) ,  lotnumber  ) 
            + case when suffix is null then '' else suffix end as Value
    from @t
    order by case when prefix is null and suffix is null then cast(lotnumber as decimal(19,2))
                 when prefix is null and suffix is not null then lotnumber + 0.5
                 else lotnumber + @maxlotnumber end , suffix ,prefix


    - Chintak (My Blog)

    Friday, July 27, 2012 10:52 AM
  • Thanks for all help,

    i tried this code and it worked

    SELECT lotnumber
    FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY lotnumber, Prefix, Suffix ) AS RowNum ,
    ( CASE WHEN prefix IS NULL THEN ''
    ELSE prefix
    END ) + CONVERT (NVARCHAR, ( lotnumber ))
    + ( CASE WHEN suffix IS NULL THEN ''
    ELSE suffix
    END ) AS lotnumber ,
    lotnumber AS OrderBylotnumber
    FROM lots2
    ) s
    ORDER BY CASE WHEN lotnumber LIKE '[0-9]%' THEN 1
    ELSE 2
    END ,
    RowNum


    Regards Ismail

    • Proposed as answer by Shulei ChenEditor Thursday, August 2, 2012 5:37 AM
    • Marked as answer by Maggie Luo Friday, August 3, 2012 9:52 AM
    Friday, July 27, 2012 12:24 PM

All replies

  • Hi,

    See if this helps

    --Sample Data ( added few more rows for test)
    declare @t table
    ( 
    prefix  nvarchar(1)
    , lotnumber int
    , suffix nvarchar(1)
    )
    
    insert into @t
    select 'a',1,'a'
    union all
    select NULL,1,'a'
    union all
    select NULL,1, NULL
    union all
    select NULL,1, 'c'
    union all
    select NULL,2,NULL
    union all
    select NULL,'100',NULL
    union all
    select 'a','1','b'
    
    --Actual Query 
    declare @maxlotnumber decimal(19,2)
    select @maxlotnumber = 2147483647.0 --max integer value possible
    
    select case when prefix is null then '' else prefix end 
            +  CONVERT ( nvarchar(10) ,  lotnumber  ) 
            + case when suffix is null then '' else suffix end as Value
    from @t
    order by case when prefix is null and suffix is null then cast(lotnumber as decimal(19,2))
                 when prefix is null and suffix is not null then lotnumber + 0.5
                 else lotnumber + @maxlotnumber end , suffix ,prefix


    - Chintak (My Blog)

    Friday, July 27, 2012 10:52 AM
  • Thanks for all help,

    i tried this code and it worked

    SELECT lotnumber
    FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY lotnumber, Prefix, Suffix ) AS RowNum ,
    ( CASE WHEN prefix IS NULL THEN ''
    ELSE prefix
    END ) + CONVERT (NVARCHAR, ( lotnumber ))
    + ( CASE WHEN suffix IS NULL THEN ''
    ELSE suffix
    END ) AS lotnumber ,
    lotnumber AS OrderBylotnumber
    FROM lots2
    ) s
    ORDER BY CASE WHEN lotnumber LIKE '[0-9]%' THEN 1
    ELSE 2
    END ,
    RowNum


    Regards Ismail

    • Proposed as answer by Shulei ChenEditor Thursday, August 2, 2012 5:37 AM
    • Marked as answer by Maggie Luo Friday, August 3, 2012 9:52 AM
    Friday, July 27, 2012 12:24 PM