Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UKTpi-0005d2-QB for pgsql-sql@arkaria.postgresql.org; Tue, 26 Mar 2013 13:22:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UKTpi-0003eM-Ak for pgsql-sql@arkaria.postgresql.org; Tue, 26 Mar 2013 13:22:34 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UKTph-0003eG-3q for pgsql-sql@postgresql.org; Tue, 26 Mar 2013 13:22:33 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UKTpd-0005JF-93 for pgsql-sql@postgresql.org; Tue, 26 Mar 2013 13:22:32 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 13CCCE00EC82 for ; Tue, 26 Mar 2013 06:22:27 -0700 (PDT) Message-ID: <5151A112.2030904@pinpointresearch.com> Date: Tue, 26 Mar 2013 06:22:26 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130308 Thunderbird/17.0.4 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: how can I replace all instances of a pattern References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------000203000109060203020208" X-Pg-Spam-Score: -3.2 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------000203000109060203020208 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 03/26/2013 06:08 AM, James Sharrett wrote: > I'm trying remove all instances of non-alphanumeric or underscore > characters from a query result for further use. This is part of a > function I'm writing 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:= 'select column_name from mytable'; > > for sql_record in execute sql_qry loop > curr_record := sql_record.column_name; > > while length(substring(curr_record from '\W'))>0 loop > curr_record := regexp_replace(curr_record, '\W',''); > end loop; > > .... 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 can base it on a regular expression pattern. Is there a > better way to do this in 9.1? You were on the right track with regexp_replace but you need to add a global flag: regexp_replace(column_name,'\W','','g') See examples under http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP Cheers, Steve --------------000203000109060203020208 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 03/26/2013 06:08 AM, James Sharrett wrote:
I'm trying remove all instances of non-alphanumeric or underscore characters from a query result for further use.  This is part of a function I'm writing 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:= 'select column_name from mytable';

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

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

…. 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 can base it on a regular expression pattern.  Is there a better way to do this in 9.1?

You were on the right track with regexp_replace but you need to add a global flag:
regexp_replace(column_name,'\W','','g')

See examples under http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Cheers,
Steve

--------------000203000109060203020208--