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
Error occured while replacing a word in the column
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Error occured while replacing a word in the column
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Error occured while replacing a word in the column
Hi!
Can you provide more info?
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)
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Error occured while replacing a word in the column
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.
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.
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Error occured while replacing a word in the column
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?
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.
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 ).
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 ;-)
(basing on your info too much occurrence of same word - it asks for RLE http://en.wikipedia.org/wiki/Run-length_encoding ).
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Error occured while replacing a word in the column
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
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
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1