Replace apostrophe in a dynamically created insert statement

Question

I have created a lookup table which holds Insert statements. Some of the values in the values have apostrophes in them.

The value appears like this

'SCRW, PAN-HD PHIL, THR'D: 8-32. L: 3/8""'

Whole statement is:

INSERT INTO PARTS_BOMD (BOM_ID, ITEM_REV, ITEM_CN, ITEM_NUMBER, ITEMNUMBER, FINDNUM, QTY, ITEMDESCRIPTION, ITEMREV, ITEMSIZE, REFDES, BOMTEXT02, ITEMLIST21, SUMMARYCOMPLIANCE, BOMMULTITEXT30, BOMNOTES, ITEMLIST10, BOMLIST01, BOMLIST03, BOMLIST02, ITEMTEXT22, ITEMTEXT23, ITEMLIFECYCLEPHASE, ITEMP2MULTILIST05, ITEMTEXT15, RNUM) VALUES (2009034062,'31','ECO05447','1472096','1422042','100','4','SCRW, PAN-HD PHIL, THR'D: 8-32. L: 3/8""','A0 MPL03682','PC','PC','','6 out of 6 Compliant','Missing Info','Missing Info','','ZHLB','','','X','','','AREL','Yes','0.10220',582272);

This table have more than 4 million records and this issue occurs most of the time.

Is there any way I can change this apostrophe into two single quotes.

I am using SQL Server 2014.

Oracle scripts which generates these insert statements:

set serveroutput on size 100000
set feedback off

declare
  v_table_name      varchar2(30) := 'PARTS_BOMD';  -- Your Tablename
  v_column_list     varchar2(2000);
  v_insert_list     varchar2(2000);
  v_ref_cur_columns varchar2(4000);
  v_ref_cur_query   varchar2(2000);
  v_ref_cur_output  varchar2(2000);
  v_column_name     varchar2(2000);
  cursor c1 is select column_name, data_type from user_tab_columns where table_name = v_table_name order by column_id;
  refcur            sys_refcursor; 
begin
  for i in c1 loop
     v_column_list := v_column_list||','||i.column_name;
     if i.data_type = 'NUMBER' then
        v_column_name := i.column_name;
     elsif i.data_type = 'DATE' then
        v_column_name := chr(39)||'to_date('||chr(39)||'||chr(39)'||'||to_char('||i.column_name||','||chr(39)||'dd/mm/yyyy hh:mi:ss'||chr(39)||')||chr(39)||'||chr(39)||', '||chr(39)||'||chr(39)||'||chr(39)||'dd/mm/rrrr hh:mi:ss'||chr(39)||'||chr(39)||'||chr(39)||')'||chr(39);
     elsif i.data_type = 'VARCHAR2' then
        v_column_name := 'chr(39)||'||i.column_name||'||chr(39)';
     end if;
     v_ref_cur_columns := v_ref_cur_columns||'||'||chr(39)||','||chr(39)||'||'||v_column_name;
  end loop; 
  v_column_list     := ltrim(v_column_list,',');
  v_ref_cur_columns := substr(v_ref_cur_columns,8);

  v_insert_list     := 'INSERT INTO '||v_table_name||' ('||v_column_list||') VALUES ';
  v_ref_cur_query   := 'SELECT '||v_ref_cur_columns||' FROM '||v_table_name;

  open refcur for v_ref_cur_query;
  loop
  fetch refcur into v_ref_cur_output; 
  exit when refcur%notfound;
    v_ref_cur_output := '('||v_ref_cur_output||');'; 
    v_ref_cur_output := replace(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := replace(v_ref_cur_output,'(,','(null,');
    v_ref_cur_output := replace(v_ref_cur_output,',,)',',null)');
    v_ref_cur_output := replace(v_ref_cur_output,'null,)','null,null)');
    v_ref_cur_output := v_insert_list||v_ref_cur_output; 
    --dbms_output.put_line (v_ref_cur_output); 
    INSERT INTO BOM_INS_LOOKUP(LOOKUP_STATMENT)
    VALUES (v_ref_cur_output);
    COMMIT;
  end loop; 
end;
/

It ain't much but it does the job.

Answer 1

There really isn't an easy way to approach this problem generically. How would you go about processing a list of values like this one?: 'A',',','B' Is it one or two or three values? And there are two ways to split it into two values.

Here's an approach you might take by making some assumptions about the format.

declare @pos int;
declare @s varchar(1024);

declare myCursor for select <COLUMN> from T;
open myCursor;

while @@fetch_status = 0
begin 

    fetch next from myCursor into @s;

    set @pos = charindex(@s, '''', @pos);
    while @pos > 0
    begin
        /* if apostrophe is followed by comma plus apostrophe
           then assume this is the delimiter */
        if substring(@s +',''', @pos + 1, 2) <> ','''
            set @s = stuff(@s, @pos, 1, '''''');
        else
            set @pos = @pos + 2;
       set @pos = charindex(@s, '''', @pos);
    end

    update T set <COLUMN> = @s where current of myCursor;
end

close myCursor;
deallocate myCursor;

It would be better to avoid the problem in the first place by properly quoting the values as the INSERT queries are generated. There are many ways to export data from Oracle that can be readily picked up by SQL Server.