Home » SQL & PL/SQL » SQL & PL/SQL » Replace and underscores
Replace and underscores [message #261994] Fri, 24 August 2007 05:41 Go to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
I am trying to make a function for giving in a text with some substitution strings, and fill it with data from the database. My substitution strings are in the format _string_

The problem is that even the occurrences of string that do not have the underscores around them are replaced. This is my function:

create or replace
function get_emailing_text(
        action_id_in              action.action_id%type,
        emailing_text_in          clob)
return clob
is
        cursor action_info_cur is
            select nvl(A.contact_id, I.contact_id) contact_id, 
                I.item_id,
                M.manufacturer_name, 
                C.manuf_part_no, 
                I.manuf_serial_no, 
                lpad(lhc_inventory_no, 4, '0') || ' ' || lpad(lhc_serial_no, 4, '0') lhc_inv_serial,
                A.report, 
                action_type_id
            from action A, item I, component C, manufacturer M
            where A.action_id = action_id_in
              and A.item_id = I.item_id
              and I.component_id = C.component_id
              and C.manufacturer_id = M.manufacturer_id;
        
        action_info_row           action_info_cur%rowtype;
        
        v_emailing_text           clob; 

        v_contact_last_name       persons.last_name%type;
        v_contact_first_name      persons.first_name%type;
begin
        v_emailing_text := emailing_text_in;
        
        open action_info_cur;
        fetch action_info_cur into action_info_row;
        close action_info_cur;
        
        select first_name, last_name 
        into v_contact_first_name, v_contact_last_name
        from contact C, persons P
        where C.contact_id = action_info_row.contact_id 
          and C.person_id = P.person_id;
          
        if v_emailing_text is not null then
            v_emailing_text := 
              replace(v_emailing_text, '_contact_name_',  
                      initcap(v_contact_first_name || ' ' ||    v_contact_last_name));
            v_emailing_text := 
              replace(v_emailing_text, '_manuf_part_no_',    action_info_row.manuf_part_no);
            v_emailing_text := 
              replace(v_emailing_text, '_manuf_serial_no_',  action_info_row.manuf_serial_no);
            v_emailing_text := 
              replace(v_emailing_text, '_manuf_',            action_info_row.manufacturer_name);
            v_emailing_text := 
              replace(v_emailing_text, '_lhc_inv_serial_',   action_info_row.lhc_inv_serial);
            v_emailing_text := 
               replace(v_emailing_text, '_report_',           action_info_row.report);
        end if;
        return v_emailing_text;
end get_emailing_text;


When I try running this:
select get_emailing_text(24183,
 'I understand that your equipment (_manuf_part_no_/_manuf_serial_no_) doesn''t work. 
This is what you reported to us : _report_. 
The manufacturer for this equipment is _manuf_') text 
from dual;

I get this as the result:
I understand that your equipment (A3016/27) doesn't work. This is what youGIven to Mr Petruccid to us : GIven to Mr Petrucci. TheCAENcturer for this equipment is CAEN

The manufacturer for the action is "CAEN", the report is "GIven to Mr Petrucci" and the manufacturers part and serial number are A3016 and 27.

As you can see, both "_report_" and "report" was replaced by the report for the action. and "manuf" and "_manuf_" was replaced by CAEN. Does the replace function use the "like" syntax when it replaces? And how can I make it only replace exact matches?

[Updated on: Fri, 24 August 2007 06:00] by Moderator

Report message to a moderator

Re: Replace and underscores [message #262009 is a reply to message #261994] Fri, 24 August 2007 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not my experience:
SQL> declare string varchar2(200) := 'michel _michel_';
  2  begin
  3    string := replace(string,'_michel_','mike');
  4    dbms_output.put_line(string);
  5  end;
  6  /
michel mike

PL/SQL procedure successfully completed.

Post a test case that we can reproduce.

Regards
Michel
Re: Replace and underscores [message #262023 is a reply to message #262009] Fri, 24 August 2007 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ahhh.. it's a bit more subtle than that.
There's a difference in behaviour between REPLACE passing in a Varchar2, and REPLACE passing in a CLOB.
Somehow, REPLACE passing in a CLOB seems to treat a space as a wildcard:
SQL> declare
  2     v_clob_input     clob          := 'replace _replace_';
  3     v_string_input   varchar2(100) := 'replace _replace_';
  4     
  5     v_clob_input2     clob          := ' replace _replace_';
  6     v_string_input2   varchar2(100) := ' replace _replace_';
  7     
  8     v_string    varchar2(100);
  9     v_clob      varchar2(100);
 10  begin
 11     v_string := replace(v_string_input,'_replace_','replaced');
 12     v_clob := replace(v_clob_input,'_replace_','replaced');   
 13    
 14     dbms_output.put_line('Original String **'||v_string_input||'** Replaced String **'||v_string||'**');
 15     dbms_output.put_line('Original Clob   **'||v_clob_input  ||'** Replaced Clob   **'||v_clob||'**');
 16     
 17     v_string := replace(v_string_input2,'_replace_','replaced');
 18     v_clob := replace(v_clob_input2,'_replace_','replaced');   
 19    
 20     dbms_output.put_line('Original String **'||v_string_input2||'** Replaced String **'||v_string||'**');
 21     dbms_output.put_line('Original Clob   **'||v_clob_input2  ||'** Replaced Clob   **'||v_clob||'**');
 22  end;
 23  /
Original String **replace _replace_** Replaced String **replace replaced**
Original Clob   **replace _replace_** Replaced Clob   **replace replaced**
Original String ** replace _replace_** Replaced String ** replace replaced**
Original Clob   ** replace _replace_** Replaced Clob   **replacedreplaced**

PL/SQL procedure successfully completed.


If you do this:
SQL> declare
  2     v_clob_input     clob          := ' replace_replace_';
  3     v_string         varchar2(100);
  4  
  5  begin
  6     v_string := replace(v_clob_input,'_replace_','replaced');
  7     dbms_output.put_line('Original Clob   **'||v_clob_input  ||'** Replaced Clob   **'||v_string||'**');
  8  end;
  9  /
Original Clob   ** replace_replace_** Replaced Clob   **replacedreplace_**

PL/SQL procedure successfully completed.

You can see that it is matching the leading space as an '_'

I reckon you've found a bona fide bug here.
Hie thee to Metalink, raise a SR with them and post the test cases I've just given you.
icon7.gif  Re: Replace and underscores [message #262040 is a reply to message #262023] Fri, 24 August 2007 06:47 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Thank you Smile

It also seems that as long as there's a space in front of "replace" it doesn't look for the last underscore either.

declare
     v_clob_input     clob          := ' replaceISSTRANGE _replace_';
     v_string         varchar2(100);

begin
     v_string := replace(v_clob_input,'_replace_','replaced');
     dbms_output.put_line('Original Clob   **'||v_clob_input  ||'** Replaced Clob   **'||v_string||'**');
end;


This gives:
Original Clob ** replaceISSTRANGE _replace_** Replaced Clob **replacedSSTRANGE replaced**

So it seems it just cuts the first letter after "replace" in that case.
Re: Replace and underscores [message #262043 is a reply to message #262023] Fri, 24 August 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good catch.
It is not space but '_' is treated as a wildchar in replace with CLOB:
SQL> declare
  2    l_string  varchar2(100) := 'michel _michel_';
  3    l_clob    clob          := 'michel _michel_';
  4    l_string2 varchar2(100) := ' michel _michel_';
  5    l_clob2   clob          := ' michel _michel_';
  6    l_string3 varchar2(100) := 'XmichelY_michel_';
  7    l_clob3   clob          := 'XmichelY_michel_';
  8  begin
  9    l_string := 'michel _michel_';
 10    l_clob   := 'michel _michel_';
 11    l_string := replace (l_string, '_michel_', 'mike');
 12    l_clob   := replace (l_clob,   '_michel_', 'mike');
 13    dbms_output.put_line ('string0='||l_string);
 14    dbms_output.put_line ('clob0  ='||l_clob);
 15    l_string := ' michel _michel_';
 16    l_clob   := ' michel _michel_';
 17    l_string := replace (l_string, '_michel_', 'mike');
 18    l_clob   := replace (l_clob,   '_michel_', 'mike');
 19    dbms_output.put_line ('string1='||l_string);
 20    dbms_output.put_line ('clob1  ='||l_clob);
 21    l_string := 'XmichelY_michel_';
 22    l_clob   := 'XmichelY_michel_';
 23    l_string := replace (l_string, '_michel_', 'mike');
 24    l_clob   := replace (l_clob,   '_michel_', 'mike');
 25    dbms_output.put_line ('string3='||l_string);
 26    dbms_output.put_line ('clob3  ='||l_clob);
 27  end;
 28  /
string0=michel mike
clob0  =michel mike
string1= michel mike
clob1  =mikemike
string3=XmichelYmike
clob3  =mikemike

PL/SQL procedure successfully completed.

Regards
Michel
Re: Replace and underscores [message #262044 is a reply to message #262043] Fri, 24 August 2007 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Eetter example:
SQL> declare 
  2    l clob := 'abcdef';
  3  begin
  4    l := replace(l, 'a___e','xyz');
  5    dbms_output.put_line (l);
  6  end;
  7  /
xyzf

PL/SQL procedure successfully completed.

Regards
Michel
Re: Replace and underscores [message #262052 is a reply to message #262044] Fri, 24 August 2007 07:03 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
But how can I escape these underscores then? I tried using \ but then it doesn't replace anything.
Re: Replace and underscores [message #262053 is a reply to message #262052] Fri, 24 August 2007 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
It is not documented.
It is likely a bug.
I don't think someone foresee a syntax to workaround a bug.

Regards
Michel
Re: Replace and underscores [message #262058 is a reply to message #262053] Fri, 24 August 2007 07:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
are you ever going to be processing more than 32K of text at a single time?
If not, you could take advantage of the 32767 byte limit on Varchar2 in Pl/Sql.
Simply unload the data from the CLOB into a Vc2 at the start of the procedure, do REPLACEs with the much less buggy varchar version of REPLACE, and then reload the results into a CLOB to return them.

If you do have to process more than 32K, you can still do the above processing, but you'll need to loop through the text in 32K chunks, making sure that you don't break the string in the middle of a substitution string.

Alternatively, you could se the DBMS_LOB package with it's INSRT and SUBSTR commands to just write a replacement REPLACE command that works properly.

Are you going to raise this with Matalink as a Bug - you really should.
Re: Replace and underscores [message #262061 is a reply to message #262058] Fri, 24 August 2007 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are you going to raise this with Matalink as a Bug

I will and give back here when I have an answer.

Regards
Michel
Re: Replace and underscores [message #262450 is a reply to message #262061] Mon, 27 August 2007 01:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It's a known feature: Check out bug 4598943 ("REPLACE FUNCTION DIFFERS WITH CLOBS"). It should be fixed in 11g. Very Happy

MHE
Re: Replace and underscores [message #262452 is a reply to message #262450] Mon, 27 August 2007 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, I got no answer from Oracle still friday, not yet.
Oracle support is getting greater each day.

Regards
Michel
Re: Replace and underscores [message #262463 is a reply to message #262450] Mon, 27 August 2007 02:16 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Maaher wrote on Mon, 27 August 2007 07:38
It's a known feature: Check out bug 4598943 ("REPLACE FUNCTION DIFFERS WITH CLOBS"). It should be fixed in 11g. Very Happy

MHE

That seems to be the case Cool
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> declare
  2    l clob := 'abcdef';
  3  begin
  4    l := replace(l, 'a___e','xyz');
  5    dbms_output.put_line (l);
  6  end;
  7  /
abcdef

PL/SQL procedure successfully completed.
Previous Topic: Seperating the single column values into two columns
Next Topic: Getting consecutive records satisfying a specific conditions
Goto Forum:
  


Current Time: Sun Dec 04 12:42:59 CST 2016

Total time taken to generate the page: 0.05299 seconds