Hi,

I have a variable field of type varchar2 called storeId. I need to
sort the store Id's but they are of variable length and some of them
contain characters and digits while others contain only digits. I
tried using order by lpad(storeId, 10) asc but it does not work in the
case that 1 id is a11 and another one is b2. It gives me b2 and then
a11.

Is there a way to fix this?

Thank you

On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote:
Assuming you are using an ASCII based character set, space comes
before the letters so ' b2' comes before 'a11' (even before 'b11').

Try RPAD() ?

HTH,
ed

On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote:
Assuming you are using an ASCII based character set, space comes
before the letters so ' b2' comes before 'a11' (even before 'b11').

Try RPAD() ?

HTH,
ed

On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote:
Assuming you are using an ASCII based character set, space comes
before the letters so ' b2' comes before 'a11' (even before 'b11').

Try RPAD() ?

HTH,
ed

On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote:
Assuming you are using an ASCII based character set, space comes
before the letters so ' b2' comes before 'a11' (even before 'b11').

Try RPAD() ?

HTH,
ed

C-man <c00ldia... (AT) gmail (DOT) com> wrote:
I think you need to give a clearer example. Are you
sorting by number, then by text, or vice versa?

I had a similar problem sorting legislative act sections.
I used regular expressions (10g)...

with
blah as
(
select to_char(level) x from dual
connect by level <= 10 union all
select to_char(level) || 'A' from dual
connect by level between 2 and 5 union all
select to_char(level) || 'B' from dual
connect by level between 4 and 5
)
select x
from blah
order by
to_number(regexp_substr(x, '[0-9]+')),
lower(x) nulls first;

To do a text comparison in the absense of the number,
you can use regexp_replace(x, '[0-9]+', null) to
extract the text component. [That's also what you'll
need if you allow 9a and 009a say in your normal ids
and you want them to compare equal. The simple second
ordering by x, or lower(x) in the sample above, will
fail.]

You say that there are always digits, but you can also
compare cases where they may not be a digit if you know
the digits (if present) will be always be prefix, or
always suffix...

-- prefix
to_number(to_number(regexp_substr('0' || x, '[0-9]+')))

-- suffix
to_number(to_number(regexp_substr(x || '0', '[0-9]+'))) / 10

--
Peter

C-man <c00ldia... (AT) gmail (DOT) com> wrote:
I think you need to give a clearer example. Are you
sorting by number, then by text, or vice versa?

I had a similar problem sorting legislative act sections.
I used regular expressions (10g)...

with
blah as
(
select to_char(level) x from dual
connect by level <= 10 union all
select to_char(level) || 'A' from dual
connect by level between 2 and 5 union all
select to_char(level) || 'B' from dual
connect by level between 4 and 5
)
select x
from blah
order by
to_number(regexp_substr(x, '[0-9]+')),
lower(x) nulls first;

To do a text comparison in the absense of the number,
you can use regexp_replace(x, '[0-9]+', null) to
extract the text component. [That's also what you'll
need if you allow 9a and 009a say in your normal ids
and you want them to compare equal. The simple second
ordering by x, or lower(x) in the sample above, will
fail.]

You say that there are always digits, but you can also
compare cases where they may not be a digit if you know
the digits (if present) will be always be prefix, or
always suffix...

-- prefix
to_number(to_number(regexp_substr('0' || x, '[0-9]+')))

-- suffix
to_number(to_number(regexp_substr(x || '0', '[0-9]+'))) / 10

--
Peter

C-man <c00ldia... (AT) gmail (DOT) com> wrote:
I think you need to give a clearer example. Are you
sorting by number, then by text, or vice versa?

I had a similar problem sorting legislative act sections.
I used regular expressions (10g)...

with
blah as
(
select to_char(level) x from dual
connect by level <= 10 union all
select to_char(level) || 'A' from dual
connect by level between 2 and 5 union all
select to_char(level) || 'B' from dual
connect by level between 4 and 5
)
select x
from blah
order by
to_number(regexp_substr(x, '[0-9]+')),
lower(x) nulls first;

To do a text comparison in the absense of the number,
you can use regexp_replace(x, '[0-9]+', null) to
extract the text component. [That's also what you'll
need if you allow 9a and 009a say in your normal ids
and you want them to compare equal. The simple second
ordering by x, or lower(x) in the sample above, will
fail.]

You say that there are always digits, but you can also
compare cases where they may not be a digit if you know
the digits (if present) will be always be prefix, or
always suffix...

-- prefix
to_number(to_number(regexp_substr('0' || x, '[0-9]+')))

-- suffix
to_number(to_number(regexp_substr(x || '0', '[0-9]+'))) / 10

--
Peter

C-man <c00ldia... (AT) gmail (DOT) com> wrote:
I think you need to give a clearer example. Are you
sorting by number, then by text, or vice versa?

I had a similar problem sorting legislative act sections.
I used regular expressions (10g)...

with
blah as
(
select to_char(level) x from dual
connect by level <= 10 union all
select to_char(level) || 'A' from dual
connect by level between 2 and 5 union all
select to_char(level) || 'B' from dual
connect by level between 4 and 5
)
select x
from blah
order by
to_number(regexp_substr(x, '[0-9]+')),
lower(x) nulls first;

To do a text comparison in the absense of the number,
you can use regexp_replace(x, '[0-9]+', null) to
extract the text component. [That's also what you'll
need if you allow 9a and 009a say in your normal ids
and you want them to compare equal. The simple second
ordering by x, or lower(x) in the sample above, will
fail.]

You say that there are always digits, but you can also
compare cases where they may not be a digit if you know
the digits (if present) will be always be prefix, or
always suffix...

-- prefix
to_number(to_number(regexp_substr('0' || x, '[0-9]+')))

-- suffix
to_number(to_number(regexp_substr(x || '0', '[0-9]+'))) / 10

--
Peter

On Feb 13, 9:23 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
rpad will work in that case but for cases when the ids are 123, 12,
22, 223 and 4, rpad won't help. I am trying to do something similar
to the sort provided in excel.