Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Limitation of substr function

SandeepwAug 5 2010 — edited Feb 7 2011
How to use SUBSTR function for special characters, so that it will give the CORRECT result. As special character holds extra memory than normal charcter, simply using substr function gives wrong result.

Here is the example which will clear my doubt:

Line: ------------
Code:
Line: ------------
Initial values:
Variables
v_message_substr VARCHAR2(2000);
v_message VARCHAR(32767); -- this variable gets the data from external source
v_line NUMBER := 1;
v_id NUMBER := 1;
Table test_table (column1 e_id(varchar2(1), column2 message(varchar2(2000))
........
1) v_message_substr := SUBSTR(v_message,(1+((v_line-1)*2000)),2000);
2) DBMS_OUTPUT.PUT_LINE('email message string'||v_message_substr);
3) INSERT INTO test_table VALUES (v_id, vl_message_substr);

Line: ------------
The above code will work properly, even if v_message have data more than values 2000, however if v_message have special like Ë , substr will pass data more than 2000 character which will cause the code to fail at line1 as v_message_substr have max length of 2000 and substr function is passing data of length 2001.

Please help me how to use substr function efficiently so that it should not pass data more than it should pass.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 7 2011
Added on Aug 5 2010
2 comments
1,863 views