I'm having a problem sorting my data. Usually when the column follows a format, I can use a combination of INSTR and LPAD to sort correctly. In this new case, my columns does not follow a specific format.
Sample data:
with t as(
select 'xxx 10 MG yyy' sortcol from dual union
select 'xxx 5 MG yyy' from dual union
select 'xxx 28 MG yyy' from dual union
select 'aa 10% 12.5 ' from dual union
select 'aa 6.25% 12.5' from dual union
select '222 zzz 30/17.5' from dual union
select '222 zzz 30/5.5' from dual
)
select * from t
SORTCOL
222 zzz 30/17.5
222 zzz 30/5.5
aa 10% 12.5
aa 6.25% 12.5
xxx 10 MG yyy
xxx 28 MG yyy
xxx 5 MG yyy
I would like to sort the data as
222 zzz 30/5.5
222 zzz 30/17.5
aa 6.25% 12.5
aa 10% 12.5
xxx 5 MG yyy
xxx 10 MG yyy
xxx 28 MG yyy
I'm trying to replace all numerical value in the string to have 4 digits. For example:
xxx 0010 MG yyy
xxx 0028 MG yyy
xxx 0005 MG yyy
0222 zzz 0030/0017.0005
0222 zzz 0030/0005.0005
so Oracle will sort the numeric correctly as text fields. I thought I found the solution with regular expression:
select regexp_replace('x10x100x6.5x','([0-9]+)',lpad('\1', 4,0)) from dual
x0010x00100x006.005x
But the problem is that the backreference in the LPAD doesn't substitute the value in before it execute LPAD. It treats it as the string literal '\1' as oppose to the reference value: 10, 1, 6, and 5. Therefore, LPAD thinks the lenght is always 2, length('\1') = 2 instead of length('100') = 3.
Is there another method to expand all the numbers? Thank you for your time.