Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

REGEXP_REPLACE and LPAD

pshah2kJul 9 2006 — edited Jul 10 2006
Here is the situation I have a string of numbers in one of these formats,
'123','123:987', '12:123', etc. more in the example. I need to make the string like this: '0123', '0123:0987', '0012:0123'. However the REGEXP_REPLACE and LPAD is not giving me the right results.

WITH t AS
(
SELECT
COLUMN_VALUE AS value
FROM TABLE(SYS.DBMS_DEBUG_VC2COLL
( '1','12','123','1234','1:9','12:9','12:98','123:987','1234:987','1234:9876')
)
)

SELECT value, --value2,
CASE
WHEN INSTR(value, ':') = 0
THEN LPAD(value, 4, '0')
ELSE
REGEXP_REPLACE(value, '([0-9]{1,4}):([0-9]{1,4})', LPAD('\1', 4, '0') || ':' || LPAD('\2', 4, '0'))
END new_value
FROM t;

value new_value
---------- --------------
1 0001
12 0012
123 0123
1234 1234
1:9 001:009
12:9 0012:009
12:98 0012:0098
123:987 00123:00987
1234:987 001234:00987
1234:9876 001234:009876

The result should have been

value new_value
---------- --------------
1 0001
12 0012
123 0123
1234 1234
1:9 0001:0009
12:9 0012:0009
12:98 0012:0098
123:987 0123:0987
1234:987 1234:0987
1234:9876 1234:9876


The LPAD within REGEXP_REPLACE does not give me the right results.

Any suggestions.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 7 2006
Added on Jul 9 2006
3 comments
668 views