SQL & PL/SQL

Hi Folks,

I have some strings which are all a mix of letters and numbers - no particular rules, but no special characters.
For ordering purposes, I would like to be able to lpad each number string to 5 digits (in reality there will never be more than 3 digits in a row)

For example:

XX01 -> XX00001
XX100 -> XX00100
XX1YY10ZZ1 ->XX00001YY00010ZZ00001

Any thoughts on how this can be achieved?

I am currently trying to get something working with REGEXP_REPLACE but cant quite get it working:

select pc.pcode,REGEXP_REPLACE(pc.pcode, '([[:digit:]]+)',lpad('\1',5,0))
from test_tab pc
order by 2
/

The actual/desired results are as follows:

ORIG DESIRED RESULT ACTUAL RESULT
=== =========== ==========
XX01 XX00001 XX00001 (correct)
XX10 XX00010 XX00010 (correct)
XX100 XX00100 XX000100 (incorrect)
XX1YY10ZZ1 XX00001YY00010ZZ00001 XX0001YY00010ZZ0001 (incorrect)

Thanks for any input you may have!

Matt

Edited by: Matthew Morgan on Jan 10, 2013 7:59 PM

Edited by: Matthew Morgan on Jan 10, 2013 7:59 PM

Edited by: Matthew Morgan on Jan 10, 2013 8:00 PM