Cyberiafreak

"Fortunate are those who take the first steps.” ― Paulo Coelho

How to insert single quoted string in SQL Server

Today I
had a situation where I want to insert Sql Query into a db table with lot of single
quotes. Normally when we insert a string into a table, we have to enclose
within single quotes ‘ ‘, example

            (a)   insert into
broadcastmessageformat (messageqry)  values (’select * from hooter’)

but when we have that enclosing
string itself has lot of singlequotes, then it will be a problem. Example

            (b)  insert into
broadcastmessageformat (messageqry)  values (’select * from hooter where name=’Mahes’
and blog=’cyberiafreak’’)

When
you run this two queries, (a) executes and inserts normally, where (b) will
throw error message as below.

Msg 128, Level 15, State 1, Line 2

The name "select * from hooter where name=’Mahesh’ and blog=’cyberiafreak’
is not permitted in this context. Valid expressions are constants, constant expressions,
and (in some contexts) variables. Column names are not permitted.

Reason
:
(b) Since it has single quotes, its not
a qualified string for insertion.

When SET QUOTED_IDENTIFIER is ON
(default), all strings delimited by double quotation marks are interpreted as
object identifiers.

Solution
:
Traditional single quotes within double
quotes(“ ‘ “) but it will be very difficult to manage due to lot
of where condition statements for me..finally got this golden goose from net. But
here we have enclose all our strings within double quotes as below.

set quoted_identifier off <– identifiers cannot be quoted

insert into
broadcastmessageformat (usergroupid,messageqry) values (3,"select
* from hooter where name=’Mahesh’ and lastname=’kumar’")

Command(s) completed successfully.

More explanation at : http://msdn2.microsoft.com/en-us/library/ms174393.aspx

Advertisements

May 24, 2007 - Posted by | Uncategorized

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: