CODEWITHSUNDEEP
sql-serverexcel
Scott
Scott

Reputation: 2760

Excel - Generate INSERT Statements and Handle Single Quote

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

Answers (2)

Scott
Scott

Reputation: 2760

Using what @Rory said, this works:

="INSERT INTO #dreams VALUES('"&B3&"','"&C3&"','"&SUBSTITUTE(D3,"'","''")&"','"&E3&"','"&F3&"','"&G3&"','"&H3&"','"&I3&"')"

Upvotes: 0

Thom A
Thom A

Reputation: 96055

Wrap each of the column references with SUBSTITUTE and replace ' with ''. For example SUBSTITUTE(B3,"'","''").

Upvotes: 3

Related Questions