 | |
#11
 
| |||
| |||
|
Re: order by text -
02-14-2008
, 08:41 AM
|
On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote: 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 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 |
22, 223 and 4, rpad won't help. I am trying to do something similar
to the sort provided in excel.
#12
| |||
| |||
|
|
On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote: 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 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 |
22, 223 and 4, rpad won't help. I am trying to do something similar
to the sort provided in excel.
#13
| |||
| |||
|
|
On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote: 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 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 |
22, 223 and 4, rpad won't help. I am trying to do something similar
to the sort provided in excel.
#14
| |||
| |||
|
|
On Feb 13, 9:23 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote: On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote: 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 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 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. |
expressions should help. Basically create a function that takes a
storeId and returns a value that sorts the row into the proper place.
I cannot access Oracle today so I cannot provide an example. But I
would think a function that pads left with zeroes for pure numbers
and pads right for values with leading letters. Where other cases
(123a) belong is up to you.
Ed
#15
| |||
| |||
|
|
On Feb 13, 9:23 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote: On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote: 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 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 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. |
expressions should help. Basically create a function that takes a
storeId and returns a value that sorts the row into the proper place.
I cannot access Oracle today so I cannot provide an example. But I
would think a function that pads left with zeroes for pure numbers
and pads right for values with leading letters. Where other cases
(123a) belong is up to you.
Ed
#16
| |||
| |||
|
|
On Feb 13, 9:23 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote: On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote: 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 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 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. |
expressions should help. Basically create a function that takes a
storeId and returns a value that sorts the row into the proper place.
I cannot access Oracle today so I cannot provide an example. But I
would think a function that pads left with zeroes for pure numbers
and pads right for values with leading letters. Where other cases
(123a) belong is up to you.
Ed
#17
| |||
| |||
|
|
On Feb 13, 9:23 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote: On Feb 13, 9:49 pm, C-man <c00ldia... (AT) gmail (DOT) com> wrote: 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 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 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. |
expressions should help. Basically create a function that takes a
storeId and returns a value that sorts the row into the proper place.
I cannot access Oracle today so I cannot provide an example. But I
would think a function that pads left with zeroes for pure numbers
and pads right for values with leading letters. Where other cases
(123a) belong is up to you.
Ed
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.