varchar question

12 Aug 2005 1:21 PM

Nettan

Hi there

How do I get a string like this into a variable?
SELECT * FROM dbTemp WHERE field='TEST'

It's the '' that gives me problem.

/Nettan

12 Aug 2005 1:26 PM

Tibor Karaszi

SET @var = 'SELECT * FROM dbTemp WHERE field=''TEST'''

Double up each single quote in the string.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Nettan" <Net***@discussions.microsoft.com> wrote in message
news:D1B2C610-9A93-4685-854C-E63EB2C60CA6@microsoft.com...
> Hi there
>
> How do I get a string like this into a variable?
> SELECT * FROM dbTemp WHERE field='TEST'
>
> It's the '' that gives me problem.
>
> /Nettan

12 Aug 2005 1:29 PM

Dan Guzman

You can specify 2 single quotes in a literal string when one is desired in
the result. For example:

SET @MySqlStatement = 'SELECT * FROM dbTemp WHERE field=''TEST'''

Before resorting to dynamic SQL, please review Erland's article on dynamic
SQL considerations: http://www.sommarskog.se/dynamic_sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Nettan" <Net***@discussions.microsoft.com> wrote in message
news:D1B2C610-9A93-4685-854C-E63EB2C60CA6@microsoft.com...
> Hi there
>
> How do I get a string like this into a variable?
> SELECT * FROM dbTemp WHERE field='TEST'
>
> It's the '' that gives me problem.
>
> /Nettan

12 Aug 2005 1:33 PM

Cowboy (Gregory A. Beamer) - MVP

double the single quoptes to escape:

--example
DECLARE @MyVariable varchar(2000)
SET @MyVariable = 'SELECT * FROM dbTemp WHERE field=''TEST'''

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quote

"Nettan" wrote:

> Hi there
>
> How do I get a string like this into a variable?
> SELECT * FROM dbTemp WHERE field='TEST'
>
> It's the '' that gives me problem.
>
> /Nettan