Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

quotation and punctuation in sql server Expand / Collapse
Author
Message
Posted Sunday, October 28, 2012 4:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:53 AM
Points: 73, Visits: 163
Does anyone have some rules by which quotation in SQL Server works?
An example:
Select Year,
Max(Case When Name='Tour de France'Then Coureur End) As [Tour de France]
Max(Case When Name='Giro d''Italia' Then Coureur End) As [ Giro d'Italia]
etc.

I don't understand the punctuation in 'Giro d''Italia': it is Giro d' and Italia so one would expect Giro d' + Italia or 'Giro d'' + 'Italia', but not 'Giro d''Italia.
Perhaps someone has any ideas about this phenomenon.

Grz,

Robert
Post #1378063
Posted Sunday, October 28, 2012 5:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Here is an excellent read on the subject:

http://msdn.microsoft.com/en-us/library/aa259228(v=sql.80).aspx

Scroll down a little more than half the page to see examples.

Here is a quick example:
SET QUOTED_IDENTIFIER OFF 
DECLARE @Toff VARCHAR(100)
SET @Toff = 'I''ve got a lovely bunch of cocnuts'
SELECT @Toff

Result:
I've got a lovely bunch of cocnuts

edited to take result out of code block


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1378065
Posted Sunday, October 28, 2012 5:13 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 11:07 AM
Points: 710, Visits: 1,452
This is because quotation marks in SQL are done in pairs. In order to have a single quotation mark show up in your select statement it has to be escaped. The way to do this in T-SQL is by typing two single quotes.

Joie Andrew
"Since 1982"
Post #1378066
Posted Monday, October 29, 2012 2:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:53 AM
Points: 73, Visits: 163
Thanks a lot guys: there is a beginning of an understanding. Putting it into practice look at the following:

CREATE TABLE #images (files VARCHAR(500) NULL, InsertCMD
AS 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) '
+ ' SELECT ' + LEFT(files, PATINDEX('%[^0-9]%', files) - 1) + ' AS ImageID, '
+ '''' + files + ''' As ImageName, '
+ 'BulkColumn FROM OPENROWSET( Bulk '
+ '''D:\MijnDocumenten\DWCoureurLocaleData\Irfanview_20112011\' + files + ''','
+ ' SINGLE_BLOB) AS BLOB '
)

The rules are:
the contents of InsertCMD needs to be in single quotes;
the SQL-commands need no further treatment (just single quotes);
the left part don't need quotes because it is a literal;
then the part with '''' + files + ''' As ImageName, ' I don't understand: files is literal but needs double quotes?
the same in relation to the last part: why three quotes (double and a single I assume) before D:\MijnDocumenten\etc. then a single quote and at last again a triple and a single.

The only thing that is understandable is the fact that they come in pairs, so the total amount of quotes is always even. It is the placing that give me problems.

Perhaps someone can give it one further try?

Thanks in advance,

Robert
Post #1378151
Posted Thursday, November 1, 2012 3:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 9:27 AM
Points: 266, Visits: 161
r_slot,

To address your original question, each single quotation mark within a string must be preceded with the single quote literal marker:

SELECT 'Giro d''Italia'
UNION
SELECT 'This is an empty string: '''''

output:

---------------------------
Giro d'Italia
This is an empty string: ''


-gjr
Post #1380058
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse