Hi,

I have a table with a field i sort on which has values like: p100-01,
p101-02, p999-05, p1000-03.  On my order by shows these values like this:

p100-01
p1000-03
p101-02
p999-05

Is there a way around this or since its alpha numeric I am stuck with this?

Thanks

You can create a column that pads the strings out the way you want them
and do an ORDER BY it.

CASE WHEN foo_nbr LIKE 'p[0-9][0-9][0-9]-[0-9][0-9]'
           THEN SUBSTRING (foo-nbr, 1,4) + '0' + SUBSTRING (foo-nbr,
5,7)
           ELSE foo_nbr END AS sort_col

if your pattern is always as illustrated (i.e., single
char+number+dash+number) then try

order by convert(decimal(30,5), replace(substring(value,2,9), '-', '.'))

you'll want to adjust the decimal precision and scale based on how many
numbers you'll have before and after the dash, if it's constant or at
least has a specified range.

also, this will not be a fast order, except on a small result set

Kyle wrote:
Show quoteHide quote

.... ["value" in this represents the column name] ....

Trey Walpole wrote:
Show quoteHide quote

On Fri, 9 Sep 2005 13:41:47 -0600, Kyle wrote:

Show quoteHide quoteHi Kyle,

This requirement indicates that the value is not atomic, but that the
numeric parts have different meaning. Instead of using a kludge to fix
the sorting, it's probably better to split the various parts of this
data into seperate columns, and concatenate them when selecting the
data.

Best, Hugo

I developed a query to do natural alphanumeric sorting.  This query breaks
the alphanumeric string up into chunks of char's and number's, then orders by
the chunks.  It only works for the first 4 chunks.  I haven't had the time,
but I am sure that you could build a trigger to populate a,b,c and d columns
and sort by that.

Archer

select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',

CASE
WHEN    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then     CAST(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1
) AS INT)
else
cast    (
    left     (
        right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
            case
            when patindex('%[a-z, -]%', right  (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
            then patindex('%[a-z, -]%', right  (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
            else patindex('%[a-z, -]%', right  (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
            end
        )
as int)
end
as 'b',

LEFT    (
        right
            (
            right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
            len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
            )
    ,

        CASE
        WHEN     PATINDEX('%[0-9]%',
                right
                    (
                    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
                    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
                    )
                ) = 0
        THEN
            LEN(    right    (   
                    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
                    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
                    )
            )
        ELSE    PATINDEX('%[0-9]%',
                right
                    (
                    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
                    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
                    )
                ) -1
        END
    )
as 'c',

CASE
WHEN    right  (
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
, len(
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) - PATINDEX('%[0-9]%',
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) + 1 ) not like '%[a-z,-]%'
then     CAST(right  (
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
, len(
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) - PATINDEX('%[0-9]%',
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) + 1 ) AS INT)
else
cast    (
    left     (
        right  (
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
, len(
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) - PATINDEX('%[0-9]%',
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) + 1 ),
            case
            when patindex('%[a-z, -]%', right  (
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
, len(
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) - PATINDEX('%[0-9]%',
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) + 1 )) = 0
            then patindex('%[a-z, -]%', right  (
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
, len(
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) - PATINDEX('%[0-9]%',
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) + 1 ))
            else patindex('%[a-z, -]%', right  (
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
, len(
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) - PATINDEX('%[0-9]%',
right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    )
) + 1 )) -1
            end
        )
as int)
end
as 'd',

right    (   
    right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
    len(right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right  (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
    ) as 'cTEST'

from tblsamplesTEST
order by a,b,c,d

Show quoteHide quote

Kyle wrote:

If only the first number has a variable length, but the second number is
always two digits:

order by len(i), i

Dieter

How to write results of a sp/query to multiple .csv/xls files
What is SQL Server version of Oracle's CPU_PER_CALL, or how do I restrict max. exec. time of a query
SImple code error
How Do I Avoid Update Deadlock
ordered insert - select is not inserting in order