Thread: regexp_replace: LF, CR, or tab

Hi!

I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string "A\t\n\rB"
becomes"AB".  The following statement seems to be not working. What mistake have I made? 

TIA

CN
========

select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' ');
 regexp_replace
----------------
 A
         B
(1 row)

2010/2/27  <seiliki@so-net.net.tw>:
> Hi!
>
> I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string
"A\t\n\rB"becomes "AB".  The following statement seems to be not working. What mistake have I made? 
>
> TIA
>
> CN
> ========
>
> select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' ');
>  regexp_replace
> ----------------
>  A
>         B
> (1 row)
>


Try:
select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' ','g');

Osvaldo

Just for clarity: In the documentation
(http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP)
is mentioned "Flag g causes the function to find each match in the
string, not only the first one, and return a row for each such match. "
So in your example it was only matching \r so A\n\tB was returned

Cheers,

Bastiaan



Osvaldo Kussama wrote:
> 2010/2/27  <seiliki@so-net.net.tw>:
>
>> Hi!
>>
>> I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string
"A\t\n\rB"becomes "AB".  The following statement seems to be not working. What mistake have I made? 
>>
>> TIA
>>
>> CN
>> ========
>>
>> select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' ');
>>  regexp_replace
>> ----------------
>>  A
>>         B
>> (1 row)
>>
>>
>
>
> Try:
> select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' ','g');
>
> Osvaldo
>
>