How to sort alphanumeric values

  • ManoharV

    SSCommitted

    Points: 1730

    I have one field which stores alphanumeric values like

    Schedule1

    Schedule13

    Schedule2

    Schedule3

    Schedule14,etc..

    If I sort this in my query it will sort as follows

    Schedule1

    Schedule13

    Schedule14

    Schedule2

    Schedule3

    But I want records in following order

    Schedule1

    Schedule2

    Schedule3

    Schedule13

    Schedule14

    Is it possible to sort as above...?

  • SQL_DBA_3

    SSCarpal Tunnel

    Points: 4660

    Please post the query in which you sorted .With an ORDER BY clause you can get the results you desired.

  • pawan.falor

    Old Hand

    Points: 307

    Hope you're looking for this...

    SELECT {ColumnName}

    FROM {TableName}

    WHERE {ColumnName} LIKE 'Schedule%'

    ORDER BY CAST(REPLACE({ColumnName},'Schedule','') AS INT)

  • ManoharV

    SSCommitted

    Points: 1730

    this is the query

    SELECT [ScheduleID],ScheduleName FROM [Schedule] ORDER BY schedulename ASC

    and this is the result

    1 Schedule1

    3 Schedule12

    4 Schedule14

    2 Schedule2

    5 Schedule31

    I want output in this format

    1 Schedule1

    2 Schedule2

    3 Schedule12

    4 Schedule14

    5 Schedule31

  • ManoharV

    SSCommitted

    Points: 1730

    And I just specified name as "schedule"....

    It may be some other name also........So I cant find that name dynamically...

  • pawan.falor

    Old Hand

    Points: 307

    Ok πŸ™‚ Try out this..

    SELECT [ScheduleID],ScheduleName

    FROM [Schedule]

    ORDER BY CAST(CASE

    WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,7)

    WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,6)

    WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,5)

    WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,4)

    WHEN ScheduleName LIKE '%[0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,3)

    WHEN ScheduleName LIKE '%[0-9][0-9]' THEN RIGHT(ScheduleName ,2)

    WHEN ScheduleName LIKE '%[0-9]' THEN RIGHT(ScheduleName ,1)

    ELSE NULL END AS INT)

    Note: It would be better to implement the whole CASE part as a scalar function to completely encapsulate the logic and simply re-use the function wherever such sorting required.

  • ManoharV

    SSCommitted

    Points: 1730

    Thanks Pawan... its working......:-D

  • mithun.gite

    SSCrazy

    Points: 2333

    HEY DUDE,

    ITS WORKING VERY FINE,,,GOOD ONE GRETA...

    I HAVE NEVER TRIED THIS WAY SO IM VERY GALD TO SEE IT..

    REGARDS,

    mITHUN

  • arun.sas

    SSChampion

    Points: 11831

    Hi Manohar,

    Try this

    declare @abc table

    (

    name1 varchar(10)

    )

    insert into @abc values ('NAME1')

    insert into @abc values ('NAME10')

    insert into @abc values ('NAME11')

    insert into @abc values ('NAME111')

    insert into @abc values ('NAME2')

    insert into @abc values ('NAME5')

    insert into @abc values ('NAME3')

    insert into @abc values ('NAME4')

    insert into @abc values ('NAME6')

    insert into @abc values ('NAME7')

    --

    select * from @abc

    ORDER BY cast (substring(name1,(PATINDEX('%[0-9]%',name1)),len(name1))as int)

    ARUN SAS

  • ManoharV

    SSCommitted

    Points: 1730

    Thanks Arun....

    Your code works fine.........

    I will implement this in my procedure....:-P

  • ChiragNS

    One Orange Chip

    Points: 26137

    arun.sas (4/9/2009)


    Hi Manohar,

    Try this

    declare @abc table

    (

    name1 varchar(10)

    )

    insert into @abc values ('NAME1')

    insert into @abc values ('NAME10')

    insert into @abc values ('NAME11')

    insert into @abc values ('NAME111')

    insert into @abc values ('NAME2')

    insert into @abc values ('NAME5')

    insert into @abc values ('NAME3')

    insert into @abc values ('NAME4')

    insert into @abc values ('NAME6')

    insert into @abc values ('NAME7')

    --

    select * from @abc

    ORDER BY cast (substring(name1,(PATINDEX('%[0-9]%',name1)),len(name1))as int)

    ARUN SAS

    HAH you beat me to it. I was trying the same thing myself. The solution posted can be used is all such situations. πŸ™‚

    "Keep Trying"

  • ManoharV

    SSCommitted

    Points: 1730

    Arun....

    Ur logic works only in the case of alphanumeric values... I throws error in case of string

    The error is

    ------

    Msg 245, Level 16, State 1, Line 17

    Conversion failed when converting the varchar value 'NAM' to data type int.

  • mithun.gite

    SSCrazy

    Points: 2333

    hi manohar,

    if ur values are going to be mixed of string and alphanumeric and if u want to take care of order of alphanumeric then try this one

    ORDER BY cast (case when (PATINDEX('%[0-9]%',SCH_NAME))>0 then substring(SCH_NAME,(PATINDEX('%[0-9]%',SCH_NAME)),len(SCH_NAME) )

    else 0 end as int)

    else

    the first solution u got would still work okkkk

    thanks

    Mithun

  • ChrisM@Work

    SSC Guru

    Points: 186043

    manohar (4/9/2009)


    Arun....

    Ur logic works only in the case of alphanumeric values... I throws error in case of string

    The error is

    ------

    Msg 245, Level 16, State 1, Line 17

    Conversion failed when converting the varchar value 'NAM' to data type int.

    Yeah but it's easy enough to fix:

    SET NOCOUNT ON

    DECLARE @abc TABLE

    (

    name1 varchar(10)

    )

    insert into @abc values ('NAME1')

    insert into @abc values ('NAME10')

    insert into @abc values ('NAME11')

    insert into @abc values ('NAME111')

    insert into @abc values ('NAME2')

    insert into @abc values ('NAME5')

    insert into @abc values ('NAME3')

    insert into @abc values ('NAME4')

    insert into @abc values ('NAME6')

    insert into @abc values ('NAME7')

    insert into @abc values ('NAME_')

    --

    SELECT *

    FROM @abc

    ORDER BY RIGHT('0000' + SUBSTRING(name1, ISNULL(NULLIF(PATINDEX('%[0-9]%',name1), 0), LEN(name1)+1), LEN(name1)), 4)

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • dwierenga

    SSC Veteran

    Points: 204

    It seems like all the function calls are a bit overkill IMO, unless I'm missing how they handle some edge-case.

    A simple

    ORDER BY len(column), column usually works for me for the generic case of "sorting alphanumeric values by numeric sorting".

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply