Re: RES: Using regexp_replace to remove small words

On 2010-12-13 13.24, Henrique de Lima Trindade wrote:
> Hi Peter,
>
> Your example works perfectly. But, I need Your help with on another situation.
> We're trying to create a plpgsql function with the expression. But, I'm getting a syntax error:
>
> -----------------
> create or replace function sp_remove_small_words( ptext text ) returns text immutable as
> $$
> begin
>
> return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' );
>
> end;
> $$ language plpgsql
> ;
> -----------------
> ERRO:  erro de sintaxe em ou próximo a "\"
> LINE 6: return regexp_replace( ptext, $$\y\w{1,3}\y$$, '', 'g' );

You're ending the function declaration with the $$. Using $fun$ as For
example, the following works:

create or replace function sp_remove_small_words( ptext text ) returns
text immutable as
              $fun$
                          begin
                                      return regexp_replace( ptext,
$$\y\w{1,3}\y$$, '', 'g' );                          end;
                                                                 $fun$
language plpgsql;


For details on dollar quoting see:
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

Regards,
roppert
>                                          ^
>
> ********** Error **********
>
> ERRO: erro de sintaxe em ou próximo a "\"
> SQL state: 42601
> Character: 138
>
> Thanks again!
>
>
> -----Mensagem original-----
> De: Peter Eisentraut [mailto:peter_e@gmx.net]
> Enviada em: sexta-feira, 10 de dezembro de 2010 20:59
> Para: Henrique de Lima Trindade
> Cc: pgsql-general@postgresql.org
> Assunto: Re: [GENERAL] Using regexp_replace to remove small words
>
> On fre, 2010-12-10 at 10:47 -0200, Henrique de Lima Trindade wrote:
>> I'm trying to find a regular expression that removes all small (length<  N)
>> words from a string. But, until now I've not been successful.
>
> Here is a start:
>
> select regexp_replace('Tommy Lee Jones', $$\y\w{2,3}\y$$, ' ', 'g' );
>
> If you want to normalize the spaces after the removal and handle
> beginning and end of the word, you will need to expand this to cover
> those cases, but the example should contain the key ingredients.
>
>
>

--
Regards,
Robert "roppert" Gravsjö