User-Agent: Microsoft-MacOutlook/14.3.2.130206
Date: Tue, 26 Mar 2013 09:08:34 -0400
Subject: how can I replace all instances of a pattern
From: James Sharrett <jsharrett@tidemark.net>
To: <pgsql-sql@postgresql.org>
Message-ID: <CD771612.8A7C%jsharrett@tidemark.net>
Thread-Topic: how can I replace all instances of a pattern
Mime-version: 1.0
Content-type: multipart/alternative;
	boundary="B_3447133720_23432106"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--B_3447133720_23432106
Content-type: text/plain;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

I'm trying remove all instances of non-alphanumeric or underscore character=
s
from a query result for further use.  This is part of a function I'm writin=
g
that is in plpgsql

Examples:

  Original value
    'My text1'
    'My text 2'
    'My-text-3'
    'My_text4'
    'My!text5'

   Desired
    'Mytext1'
    'Mytext2'
    'Mytext3'
    'My_text4'  (no change)
    'Mytext5'


The field containing the text is column_name.  I tried the following:

  Select regexp_replace(column_name,'\W','') from mytable

This deals with the correct characters but only does the first instance of
the character so the output is:

    'My text1'
    'Mytext 2'  (wrong)
    'Mytext-3'  (wrong)
    'My_text4'
    'My!text5'

I managed to get the desired output by writing the text into a variable
through a loop and then just keep looping on the variable until all the
characters are removed:

sql_qry:=3D 'select column_name from mytable';

for sql_record in execute sql_qry loop
curr_record :=3D sql_record.column_name;

        while length(substring(curr_record from '\W'))>0 loop
           curr_record :=3D regexp_replace(curr_record, '\W','');
        end loop;

=8A. rest of the code

This works but it seems like a lot of work to do something this simple but =
I
cannot find any function that will replace all instances of a string AND ca=
n
base it on a regular expression pattern.  Is there a better way to do this
in 9.1?


--B_3447133720_23432106
Content-type: text/html;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

<html><head></head><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: s=
pace; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size:=
 14px; font-family: Calibri, sans-serif; "><div>I'm trying remove all instan=
ces of non-alphanumeric or underscore characters from a query result for fur=
ther use. &nbsp;This is part of a function I'm writing that is in plpgsql</d=
iv><div><br></div><div>Examples:</div><div><br></div><div>&nbsp; Original va=
lue</div><div>&nbsp; &nbsp; 'My text1'</div><div>&nbsp; &nbsp; 'My text 2'</=
div><div>&nbsp; &nbsp; 'My-text-3'</div><div>&nbsp; &nbsp; 'My_text4'</div><=
div>&nbsp; &nbsp; 'My!text5'</div><div><br></div><div>&nbsp; &nbsp;Desired</=
div><div>&nbsp; &nbsp;&nbsp;'Mytext1'</div><div>&nbsp; &nbsp; 'Mytext2'</div=
><div>&nbsp; &nbsp; 'Mytext3'</div><div>&nbsp; &nbsp; 'My_text4' &nbsp;(no c=
hange)</div><div>&nbsp; &nbsp; 'Mytext5'</div><div><br></div><div><br></div>=
<div>The field containing the text is column_name. &nbsp;I tried the followi=
ng:</div><div><br></div><div>&nbsp; Select regexp_replace(column_name,'\W','=
') from mytable</div><div><br></div><div>This deals with the correct charact=
ers but only does the first instance of the character so the output is:</div=
><div><br></div><div><div>&nbsp; &nbsp; 'My text1'</div><div>&nbsp; &nbsp; '=
Mytext 2' &nbsp;(wrong)</div><div>&nbsp; &nbsp; 'Mytext-3' &nbsp;(wrong)</di=
v><div>&nbsp; &nbsp; 'My_text4'</div><div>&nbsp; &nbsp; 'My!text5'</div></di=
v><div><br></div><div>I managed to get the desired output by writing the tex=
t into a variable through a loop and then just keep looping on the variable =
until all the characters are removed:</div><div><br></div><div>sql_qry:=3D 'se=
lect column_name from mytable';</div><div><br></div><div><div>for sql_record=
 in execute sql_qry loop</div><div><span class=3D"Apple-tab-span" style=3D"white=
-space:pre">	</span>curr_record :=3D sql_record.column_name;</div><div><br></d=
iv><div>&nbsp; &nbsp; &nbsp; &nbsp; <span class=3D"Apple-tab-span" style=3D"whit=
e-space:pre">		</span>while length(substring(curr_record from '\W'))&gt;0 lo=
op</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=3D"Apple-tab=
-span" style=3D"white-space:pre">			</span>curr_record :=3D regexp_replace(curr_=
record, '\W','');</div><div>&nbsp; &nbsp; &nbsp; &nbsp; <span class=3D"Apple-t=
ab-span" style=3D"white-space:pre">		</span>end loop;</div></div><div><br></di=
v><div>&#8230;. rest of the code</div><div><br></div><div>This works but it =
seems like a lot of work to do something this simple but I cannot find any f=
unction that will replace all instances of a string AND can base it on a reg=
ular expression pattern. &nbsp;Is there a better way to do this in 9.1?</div=
></body></html>

--B_3447133720_23432106--