Help with Regular Expressions

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Tue, 23 Dec 2008 11:46:39 -0500
Message-ID: <0w84l.6688$8_3.4851@flpi147.ffdc.sbc.com>

Oracle 10.20.03.0, 64 bit
Windows 2003 Server

I'm trying to use regular expressions (REGEXP_REPLACE) to transform rows like the following
from rows with multiple values to one value per row. I've used similar syntax to extract
first and last names but this is not working at all this time.

I'm not too experienced with regular expressions so I know it's probably due to my lack of
experience and would appreciate any help that can be offered. I thought regular expressions suited what
I need to accomplish but if there is an easier/better way ...

The syntax I've been trying to use (which didn't work) was:

SELECT REGEXP_REPLACE(product_desc,'(.+),(.+),(.+),(.+),(.+),(.+)','\1') FROM work_table;

PRODUCT_DESC

---

XXX10, XX10, XX50, 10XX15, XXX250
XXX1, 10XXXX, XX25, XX15, XXXX
XXX10, XX20, XX75, XX10
XXX5, XXXX10, 500XX, XX25, XX150, XX50, 100XX, 40XX,XXX20 XXX10, XX50, XX10, XX35, 30XX
XXX5, XXX20, XX5, XX5, XX120
XXX10, XX100, XX10, XX35, 1500X, 30%XX, 1500XX XXX10, XX10, XX100, XX50, XX20, 30XX, 500XXX, 10%XX, 1500XX XXX5
XXX5, XX120, XXXXXX, XXXX, XX5, XX5, XXX5 XXX10, XX50, XX10, XXXXXX PRODUCT_DESC

---

XXX10
XX10
XX50
10XX15
XXX250
XXX1
10XXXX
XX25
XX15
XXXX
XXX10
XX20
XX75
XX10
XXX5
XXXX10
500XX
XX25
XX150
XX50
100XX
40XX
XXX20
XXX10
XX50
XX10
XX35
30XX
XXX5
XXX20
XX5
XX5
XX120
XXX10
XX100
XX10
XX35
1500X
30%XX
1500XX
XXX10
XX10
XX100
XX50
XX20
30XX
500XXX
10%XX
1500XX
XXX5
XXX5
XX120
XXXXXX
XXXX
XX5
XX5
XXX5
XXX10
XX50
XX10
XXXXXX Received on Tue Dec 23 2008 - 10:46:39 CST