在动态创建的插入语句中替换撇号

2021-09-10 00:00:00 sql tsql sql-server

我创建了一个查找表，其中包含 Insert 语句.值中的某些值带有撇号.

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

值是这样的

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

整个声明是:

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);

这个表有超过 400 万条记录，这个问题经常发生.

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.

我使用的是 SQL Server 2014.

I am using SQL Server 2014.

生成这些插入语句的 Oracle 脚本:

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.

推荐答案

真的没有一种简单的方法可以通用地解决这个问题.您将如何处理这样的值列表?:'A',',','B' 是一个还是两个或三个值?并且有两种方法可以将其拆分为两个值.

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;

最好通过在生成 INSERT 查询时正确引用值来首先避免该问题.有多种方法可以从 Oracle 导出数据，SQL Server 可以轻松获取这些数据.

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.

相关文章