Double Quotes!

What is the normal procedure for enclosing string variables in Dynamic TSQL.
I've tried using:
SET QUOTED_IDENTIFIER ON
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = "o" ' + //problem here!!!
Cheers,
Adam'qtype = ''o'''
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eSkxjO8xFHA.2008@.TK2MSFTNGP10.phx.gbl...
> What is the normal procedure for enclosing string variables in Dynamic
> TSQL.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
>|||Use 2 single-quotes when one is desired in the resultant string. For
example:
SET QUOTED_IDENTIFIER ON
declare @.sqlquery varchar(1000)
declare @.oqs int
SET @.oqs = 0
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = ''o'' '
SELECT @.sqlquery
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eSkxjO8xFHA.2008@.TK2MSFTNGP10.phx.gbl...
> What is the normal procedure for enclosing string variables in Dynamic
> TSQL.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
>|||On Mon, 3 Oct 2005 12:39:00 +1000, Adam Knight wrote:

> What is the normal procedure for enclosing string variables in Dynamic TSQ
L.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
Use two single-quotes instead of the double-quotes.
Like
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = ''o'' '
Ayyappan Nair

Double quotes replacement

Hi,

It seems to be simple, however, it stumbles me.
how to replace all the double quotes (") within the following
sentence (or a column) with single quotes ('),

colA = this is a freaking "silly" thing to do
into
colA this is a freaking 'silly' thing to do

Select Replace(colA,'"',''')
>From tblXYZ

won't work,

Select Replace(colA,'"',"'")
>From tblXYZ

won't work neither.

How come? Thanks.You need to specify 2 single quotes within the literal string when 1 quote
is desired. Try:

Select Replace(colA,'"','''')
From tblXYZ

--
Hope this helps.

Dan Guzman
SQL Server MVP

"NickName" <dadada@.rock.com> wrote in message
news:1125889758.581518.99700@.g49g2000cwa.googlegro ups.com...
> Hi,
> It seems to be simple, however, it stumbles me.
> how to replace all the double quotes (") within the following
> sentence (or a column) with single quotes ('),
> colA = this is a freaking "silly" thing to do
> into
> colA this is a freaking 'silly' thing to do
> Select Replace(colA,'"',''')
>>From tblXYZ
> won't work,
> Select Replace(colA,'"',"'")
>>From tblXYZ
> won't work neither.
> How come? Thanks.|||Thank you very much, Dan, works perfect.

On a related note, BOL does not cover it, how could one find a solution
to a problem similar to this one without resorting to this NG?|||I found this from the 'quotation marks' entry in the Books Online index.
From the 'Using char and varchar data' topic:

<Excerpt href="http://links.10026.com/?link=acdata.chm::/ac_8_con_03_7mch.htm">
When using single quotation marks to delimit a character constant that
contains an embedded single quotation mark, use two single quotation marks
to represent the embedded single quotation mark, for example:

SET @.MyCharVar = 'O''Leary'
</Excerpt
--
Hope this helps.

Dan Guzman
SQL Server MVP

"NickName" <dadada@.rock.com> wrote in message
news:1125967013.923190.247670@.g14g2000cwa.googlegr oups.com...
> Thank you very much, Dan, works perfect.
> On a related note, BOL does not cover it, how could one find a solution
> to a problem similar to this one without resorting to this NG?|||Man, I can't read, ok, not careful, thanks.

Double quotes in error messages!

I have a problem when trying to display an error message from sql server 2005 on a web page with an alert (javascript command).

The Sql server 2005 returns a message like:

Insert statement conflicted with foreign key constraint "bla bla". The conflict occured indatabase "databasename", table "tablename", column 'columnname'.

In sql server 2000 the error message is the same except all names (constraint, database, table) is in single quotation marks; just like the columnname in the above example.

Is it a configurable issue on the sql server. I would prefer not to solve this issue on a number of different web pages!

Thanks in advance.

Futte

hi,

you should set this:

SET QUOTED_IDENTIFIER { ON | OFF }

When on, then identifiers can be delimited by double quotation marks.

Regards,

Janos

|||

I all ready tried that without any luck.......

I want to avoid the double quotation marks.

Thanks though

Futte

|||

If you wish to 'embed' single quotes in your message, you need to double them up -use two single quotes in each place of one single quote.)

Try this:

SELECT 'This is my ''message'' with quotes.'

|||

I think my question might be a little misunderstood.

My question is not about query syntax in sql regarding single or double quotation marks or two single quotation marks.

It's about the error message sql server is returning. I suddenly experienced that after upgrading sql server from 2000 to 2005 the response from sql 2005 contained double quotation marks around all object names except for one; namely the column name:

2000:

Insert statement conflicted with foreign key constraint 'constraint name'. The conflict occurred in database 'database name', table 'table name', column 'column name'.

2005:

Insert statement conflicted with foreign key constraint "constraint name". The conflict occurred in database "database name", table "table name", column 'column name'.

This is a problem when displaying the error message on a web page with javascript (alert). So if it's a configurable issue on the sql server where is it set? Otherwise I have to handle this error message somewhere else replacing the double quotes with single quotes.

Kind Regards

Futte

|||

Thanks for the clarification. I better understand the situation.

As far as I am aware, there is no user configurable setting to alter the presentation of the error message.

Double quotes in ASCII file

I've an issue with double-quotes in CSV file. One of the columns may contain this kind of value: "STATUS ""H"" "

I've got quote set to "

The file source fails on such records.

I found this thread and Scott tells us there that the file can't contain " in data.

Is this 100% correct?

I've got mutliple text columns and the pain is that I don't know which column might have these cases in future. To create a script means to write my own file parser for all files I use.

Any ideas?

Dima.

Hi,

If you have a CSV file, do you actually need to specify the text delimeter - or do you have some text that has commas in the string as well?

If your source file contains things like

"Status ""H"" ", "Next string", 1234, etc,

then I would try to solve the problem at source. That formatting suggests to me that the data is from another database where the actual data is ,Status "H", and the CSV export is trying to escape the " character by doubling it up.

Perhaps if you can export the CSV with no text delimiters, or with a character that is guaranteed not to appear, you may be OK.

Hope this helps,

Richard

|||

Richard,

you are correct in your gueses. I do have data like this:

"Status ""H"" ", "Next string", 1234, etc,

I can't remove quoation mark since I may have data like this:

"Status ""H"" ", "Next,string", 1234, etc,

So, if I removed " I'd have another error like this:

Status "H", Next,string, 1234, etc,

So, it wouldn't parse it correctly.

I can't change quotation, symbol since I don't control source system...

Now, what I could change is data within the column. So, I could replace:

Status "H"

with something like

Status &quot;H&quot;

Then when I read this - I'd convert it back to:

Status "H"

What do you think?

Dima

|||

Dima,

That looks like a solution to me - assuming you are going to make this change prior to bringing the data into the database, ie by pre-processing the CSV file.

What process are you going to use to make the changes? I'm sure you've thought of this, but if you were just to globally change "" to &quot then it will fall over when you three or more " in a row.

eg. """H"" is my status" should become "&quotH&quot is my status" , not &quot"H&quot is my status"

I've not tried it, but you can probably do this with an SSIS script of some sort, passing regular expressions. That would be better than having an external process change the file.

Regards,

Rich

|||

The file I'm dumping is on Unix. So, I was thinking about creating sed script, using regular expressions to post-process the file after it's dumped...

Thank for the feedback BTW it helped me think this thru

Double Quotes and Print

Hi:
This is a very simple thing but some how i have a mental block. what I am
trying to
do is concatenate a string that gets passed with double quotes to a single
quoted string and trying to print but I get an error. here is what I am
trying to do:
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive="c:\bak"
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
I get an error when i execute the above.
what i am looking is to print the following when i execute the above code.
so basically i want md printed with the drive name and the folder name in
double quotes besides it.
md "c:\bak"
Can anyone please fix what I am doing wrong. looks like i am missing few
quotes.
Thanks
M> SET @.cmd='md ' + @.drive
Enclose this string in single-quotes:
SET @.drive='"c:\bak"'
Hope this helps.
Dan Guzman
SQL Server MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
> Hi:
> This is a very simple thing but some how i have a mental block. what I am
> trying to
> do is concatenate a string that gets passed with double quotes to a single
> quoted string and trying to print but I get an error. here is what I am
> trying to do:
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive="c:\bak"
> SET @.cmd='md ' + @.drive
> PRINT (@.cmd)
> I get an error when i execute the above.
> what i am looking is to print the following when i execute the above code.
> so basically i want md printed with the drive name and the folder name in
> double quotes besides it.
> md "c:\bak"
> Can anyone please fix what I am doing wrong. looks like i am missing few
> quotes.
> Thanks
> M
>
>|||Hi Dan:
Thanks for the reply. I tried that already before but my problem is the
drive name is passed as a parameter @.drive name from the stored procedure an
d
so when I concatenate the variable with single quotes i get an error.
something i did like this
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive="c:\bak"
SET @.drive=''+@.drive+''
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
Which still throws an error. The issue here is the drive name is passed as a
paramater to the sproc with double quotes like
Exec testprocedure @.drive="C:\bak"
So i would either need to replace the quotes i guess.
Any suggestions?
Thanks
"Dan Guzman" wrote:

> Enclose this string in single-quotes:
> SET @.drive='"c:\bak"'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meher" <Meher@.discussions.microsoft.com> wrote in message
> news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
>
>|||Hi,
How's this?
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive='c:\bak'
SET @.cmd='md "' + @.drive + '"'
PRINT (@.cmd)
Robert
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
> Hi:
> This is a very simple thing but some how i have a mental block. what I am
> trying to
> do is concatenate a string that gets passed with double quotes to a single
> quoted string and trying to print but I get an error. here is what I am
> trying to do:
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive="c:\bak"
> SET @.cmd='md ' + @.drive
> PRINT (@.cmd)
> I get an error when i execute the above.
> what i am looking is to print the following when i execute the above code.
> so basically i want md printed with the drive name and the folder name in
> double quotes besides it.
> md "c:\bak"
> Can anyone please fix what I am doing wrong. looks like i am missing few
> quotes.
> Thanks
> M
>
>|||Hi Robert:
The variable is passed in double quotes to the sproc and so i can have
single quotes around the c:\bak. Anyway I figured it out. I turn off the
Quoted_Identifier off and turn it back on and then perform the concatenation
.
Here is what I do:
SET QUOTED_IDENTIFIER OFF
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive="c:\bak"
SET @.drive=""""+@.drive+""""
SET QUOTED_IDENTIFIER ON
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
"Robert Ellis" wrote:

> Hi,
> How's this?
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive='c:\bak'
> SET @.cmd='md "' + @.drive + '"'
> PRINT (@.cmd)
> Robert
>
> "Meher" <Meher@.discussions.microsoft.com> wrote in message
> news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
>
>|||> Exec testprocedure @.drive="C:\bak"
> So i would either need to replace the quotes i guess.
No need to guess. There will be no quotes around the @.drive value with the
code you posted. The quotes (double or single) around the procedure
parameter value are used only as string enclosures and will not be included
in the value passed to the proc. All you need to do is add double-quotes
around the @.drive value:
ALTER PROC testprocedure
@.drive varchar(255)
AS
DECLARE @.cmd varchar(200)
SET @.drive='"'+@.drive+'"'
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
GO
EXEC testprocedure @.drive="C:\bak"
GO
By the way, it's a good practice to use only single-quotes to encclose
string literals. You'll get the same result with:
EXEC testprocedure @.drive='C:\bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:4B8493D1-412F-4DA9-B1C1-BCCE0A8C126C@.microsoft.com...
> Hi Dan:
> Thanks for the reply. I tried that already before but my problem is the
> drive name is passed as a parameter @.drive name from the stored procedure
> and
> so when I concatenate the variable with single quotes i get an error.
> something i did like this
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive="c:\bak"
> SET @.drive=''+@.drive+''
> SET @.cmd='md ' + @.drive
> PRINT (@.cmd)
> Which still throws an error. The issue here is the drive name is passed as
> a
> paramater to the sproc with double quotes like
> Exec testprocedure @.drive="C:\bak"
> So i would either need to replace the quotes i guess.
> Any suggestions?
> Thanks
> "Dan Guzman" wrote:
>

double quotes

Hi,
I am creating a flat file connection to a .csv file
In the columns section of the flatt file connection manager editor, I am not sure why the texts in the .csv file are shown with double quotes arouond them.
They do not have "" in the .csv file.
ThanksDo you have " set as the text qualifier for the flat file connection manager? You may have to put this in there. Also, how are you looking at and/or creating the csv file? If you are using Excel, it will automatically enclose text with " and when you view it in Excel it will parse it for you. Try looking at the csv file using Notepad or another basic text editor.