newtoperlpgm
asked on
Oracle SQL character data need sorted numerically
I have a column in my database that has data that begins with numbers but can have letters, that is why it is a varchar field. For example, 1, 1a, 2, 2a, 3, 3a, 4,5,6, 7, 8, 9 10
I need to sort it in ascending order, but it sorts like the following:
1
10
2
20
3
30
How can I get it to do that.????
Thank you.
I need to sort it in ascending order, but it sorts like the following:
1
10
2
20
3
30
How can I get it to do that.????
Thank you.
select * from yourtable order by to_number(regexp_replace(y ourcharact erfield,'[ ^0-9]'));
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
is there way to select it as a number rather than having to order by?
ASKER
I tried the following but it doesn't work.
select regexp_subst(SAMPLE_NO,'^[ 0-9]+') from Table
Thanks.
select regexp_subst(SAMPLE_NO,'^[
Thanks.
put either of the conditions I used in the order by
select to_number(regexp_substr(yo urcharacte rfield,'^[ 0-9]+')) from yourtable
will return '123abc' as 123
when you say "doesn't work" - what doesn't work about it?
wrong results, error, no results, keyboard catches fire?
select to_number(regexp_substr(yo
will return '123abc' as 123
when you say "doesn't work" - what doesn't work about it?
wrong results, error, no results, keyboard catches fire?
simple test cases of either method
select to_number(regexp_substr(sa mple_no,'^ [0-9]+')) from
(select '123abc' sample_no from dual);
select to_number(regexp_replace(s ample_no,' [^0-9]')) from
(select '123abc' sample_no from dual)
select to_number(regexp_substr(sa
(select '123abc' sample_no from dual);
select to_number(regexp_replace(s
(select '123abc' sample_no from dual)