RES: Using regexp_replace to remove small words

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' );
^

********** 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(at)gmx(dot)net]
Enviada em: sexta-feira, 10 de dezembro de 2010 20:59
Para: Henrique de Lima Trindade
Cc: pgsql-general(at)postgresql(dot)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.