Reputation: 2760
I have the following formula in an Excel spreadsheet:
="INSERT INTO #dreams VALUES('"&B3&"','"&C3&"','"&D3&"','"&E3&"','"&F3&"','"&G3&"','"&H3&"','"&I3&"')"
Works fine, basically, unless I have data like this in, for example, in D3.
US INVESTMENTS' GRADE CORPORATE BOND FUND (QUALIFIED)
In this case, the INSERT statement looks like this:
INSERT INTO #dreams VALUES('2019039','550678','US INVESTMENTS' GRADE CORPORATE BOND FUND (QUALIFIED)','F','5f','Hirofumi Nakamura','ACBD1','N')
That single quote will be interpreted as a delimeter. I need to have US INVESTEMENTS''
That is, substitute two single quotes for the one single quote.
I have tried various things, but can't get it to work.
EDIT: Would something similar to this work?
="INSERT INTO #dreams VALUES('"&B3&"','"&C3&"',=SUBSTITUTE(D2,"'","''"),'"&E3&"','"&F3&"','"&G3&"','"&H3&"','"&I3&"')"
Upvotes: 0
Views: 3563
Reputation: 2760
Using what @Rory said, this works:
="INSERT INTO #dreams VALUES('"&B3&"','"&C3&"','"&SUBSTITUTE(D3,"'","''")&"','"&E3&"','"&F3&"','"&G3&"','"&H3&"','"&I3&"')"
Upvotes: 0
Reputation: 96055
Wrap each of the column references with SUBSTITUTE
and replace '
with ''
. For example SUBSTITUTE(B3,"'","''")
.
Upvotes: 3