Error occured while replacing a word in the column

[rajasekhart]

Hi,

I am facing a problem while replacing a word in a column with another word.

Eg: select Replace(col1, 'raj','sekhar') from table1;

It is throwing the following error.

ERROR: Function replace may give a 78000-octet result; the limit is 65000 octets;
Error while executing the query.

Please tell me if we have any solution to this.

Thank you,
Rajasekhar

[id10t]

Hi!

Can you provide more info?

Code: Select all

test_db=> create table TestReplace ( col1 varchar, col2 varchar, col3 varchar);
CREATE TABLE
test_db=> copy TestReplace from stdin delimiter ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> foo,bar,baz
>> qux,zoo,moo
>> foo,hey,doc
>> try,foo,rep
>> \.

Code: Select all

test_db=> select *, REPLACE(col1, 'foo','xxx') as 'RAPLACE foo -> xxx' from TestReplace ;
 col1 | col2 | col3 | RAPLACE foo -> xxx 
------+------+------+--------------------
 foo  | bar  | baz  | xxx
 foo  | hey  | doc  | xxx
 qux  | zoo  | moo  | qux
 try  | foo  | rep  | try
(4 rows)

[rajasekhart]

hi..

Here i am having a table named EA_QM_Definition in schema named SMP1.
In that table(EA_QM_Definition) i am having a column named Numerator_View_Query.

This column (Numerator_View_Query) containg data of 2000 characters approximately.

so , in that text i want to replace a word. so i am using replace function as follows.

Eg: select Replace (Numerator_View_Query,'SMP1','BREVARD') from EA_QM_Definition;

but it is throwing as follows.
ERROR: Function replace may give a 78000-octet result; the limit is 65000 octets;
Error while executing the query.

[id10t]

Hi, rajasekhart!

Result of SELECT exceeds a VARCHAR limit length, it because length of 'SMP1' is 4 and length of 'BREVARD' is 7 and result string length of 78000-octet.

Hmmm... but what interesting: source string about 2000 chars length, result string about 78000 chars length. A little math?

Code: Select all

2000 * (length('brevard') - length('smp1')) = 6000
78000 - 6000 = 72000 

So even if each char was replaced by 3 others I can't get 78000-octet length string.

PS
Let source string be 20000 char length and contains 'smp1' chars sequence only.

Code: Select all

20000 / length('smp1') = 20000 / 4 = 5000        // total 'smp1' occurrence in string
5000 * length('brevard') = 35000                 // after replace 
35000 * 1.25 =  43750 < 65000                    // even 25% of error if I'm wrong ;-)

PPS You can't exceed limits. Think about how you can improve your scheme or about some workaround. IMHO such field needs improvement
(basing on your info too much occurrence of same word - it asks for RLE http://en.wikipedia.org/wiki/Run-length_encoding ).

[rajasekhart]

Hi skwa,

My problem got solved in the following way.

I used REGEXP_REPLACE() function instead of Replace() function in the select statement. It worked fine.

Eg: select REGEXP_REPLACE(Numerator_View_Query,'SMP1','BREVARD') from EA_QM_Definition;

Thank you,
Rajasekhar