How to Insert Text with Single Quotation SQL Server 2005

Way to insert text having ' (apostrophe) into a SQL table

In SQL, the way to do this is to double the apostrophe:

'he doesn''t work for me'

However, if you are doing this programmatically, you should use an API that accepts parameters and escapes them for you automatically. Programmatically escaping and using string concatenation to assemble a query yourself is a sure way to end up with SQL injection vulnerabilities.

How to concatenate string in single quotation in SQL server 2005?

To escape a single quote in SQL, you have to use the same character twice. '' will be substituted as one single quote in your concatenated string.

Single and double quotes in Sql Server 2005 insert query

You escape ' as ''.

So instead of str.Replace("\'", " ") use str.Replace("'", "''")

How to Post a SQL String With Quotes?

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

---

Related Topics