Re: Help with Regular Expressions

On Dec 23, 11:46 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> 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
Regular expressions aren't going to do that without some extra work...they're only going to reformat the string, not split it into multiple strings.

Check out this page -- you'll probably have to rewrite the function and replace the :blank: with a comma: http://www.adp-gmbh.ch/blog/2006/08/20.php

Jon Received on Tue Dec 23 2008 - 13:08:29 CST