How to Post a SQL String With Quotes?

Question

This seems like it would be a question that has been answered a million times before, but searching for a bit only brought up replacing single quotes with double quotes.

The problem is that I have a file with a huge number of single quotes. Is there any way to insert a string as is, without the single quotes throwing an error. Any wrapper?

This is for pure SQL, I don't need to save this string from the backend or anything. I need a SQL command.

Edit: Just to clarify - the string I am trying to post is a document, in html format, with a large number of both double and single quotes, escaping each one individually would take enough time to warrant saving data in another format, which I would rather not do.

possible duplicate of [How to insert text with single quotation sql server 2005](http://stackoverflow.com/questions/775687/how-to-insert-text-with-single-quotation-sql-server-2005) — kittykittybangbang, Jul 15 '15 at 18:26
What do you mean, you have a file? Is it a flat file that you are trying to import? — Tab Alleman, Jul 15 '15 at 18:29
That's a per-occurrence solution, asking if there is anything global. — VSO, Jul 15 '15 at 18:29
Escape individually? Really you don't have access to an editor that will do a bulk replace? — paparazzo, Jul 15 '15 at 18:59
@Blam, do you know far the "f' key is from "ctrl"? I am not a gymnast. — VSO, Jul 15 '15 at 19:04

score 4 · Accepted Answer · answered Jul 15 '15 at 18:44

If you have a file accessible to the SQL Server, you can read the contents in to a variable with OPENROWSET. For example, to read a Unicode text file at C:\drop\demo.html:

DECLARE @DocumentText nvarchar(MAX);

SELECT @DocumentText = BulkColumn
FROM OPENROWSET(BULK 'C:\drop\demo.html', SINGLE_NCLOB) file;

INSERT INTO Files (Column) VALUES (@DocumentText);

Sako73 · Answer 2 · 2015-07-15T18:29:58.703

2

Can you replace the single quotes with two single quotes? That is how SQL Server escapes the single quote in a string.

declare @test_string nvarchar(max) = 'string with a single '' quote in it';
print @test_string

string with a single ' quote in it

edited Jul 15 '15 at 18:29

answered Jul 15 '15 at 18:27

Sako73

9,957
13
57
75

Familiar with that solution. Want to see if there is something else without touching every single quote. – VSO Jul 15 '15 at 18:28
@Mitch: Yea, seems like what it will be in the end. It just seemed to make sense that there would be an "ignore all single quotes" command, somehow. – VSO Jul 15 '15 at 18:32
Nope. The string you are sending is either valid SQL (no embedded quotes), you are supposed to parameterize it. (Excepting for use of things like `OPENROWSET(BULK'filename', SINGLE_BLOB)`) – Mitch Jul 15 '15 at 18:34
How are you getting this string from the file into SQL? You can probably add a replace command on the reading of the file. – Sako73 Jul 15 '15 at 18:35
Read-only user agreement, so I just save it by hand on the rare occasions when it's updated. – VSO Jul 15 '15 at 18:37

score 1 · Answer 3 · answered Jul 15 '15 at 18:26

1

You need to use double quotes:

select 'it''s a double quote'

Will return:

it's a double quote

answered Jul 15 '15 at 18:26

BlackHatSamurai

23,275
22
95
156

Familiar with that solution. Want to see if there is something else without touching every single quote. – VSO Jul 15 '15 at 18:28

score -1 · Answer 4 · answered Jul 15 '15 at 18:30

-1

Read the file, parse it, and do

    if(string.charAt(I))=="'"... charAt(I)="";

answered Jul 15 '15 at 18:30

user3498796

71
1
3

How to Post a SQL String With Quotes?

4 Answers4