Single Quotation Marks in SQL

Question

Single Quotation Marks in SQL

Viewing 15 posts - 46 through 60 (of 73 total)

You must be logged in to reply to this topic. Login to reply

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 1

Phil Parkin (7/11/2014)
Apologies - I have not read all of the thread, so this may already have been covered.
It may be of interest to note that, although they are often confused, a single quote is technically different from an apostrophe.
Apostrophe: ' (Alt-0039)
Opening Single quote: ‘ (Alt-0145)
Closing single quote: ’ (Alt-0146)
Yes, I am great fun to talk to at a party 🙂

Yep it was covered extensively. But that's ok comments are always welcome 🙂

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Kenneth.Fisher (7/11/2014)
Yep it was covered extensively. But that's ok comments are always welcome 🙂

Extensively?! Sounds like that's my bedtime reading sorted out 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

J-440512 (7/11/2014)
declare var = ...
not valid in sql 2005 ...

It's 2014 and 2005 is 5 versions ago. I think it's fair to put syntax that doesn't work in 2005 now 🙂

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 4

Phil Parkin (7/11/2014)
Kenneth.Fisher (7/11/2014)
Yep it was covered extensively. But that's ok comments are always welcome 🙂
Extensively?! Sounds like that's my bedtime reading sorted out 🙂

Well it was extensively to me 🙂 Links on proper usage even.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

fregatepllada SSCommitted Points: 1702 More actions · Answer 5

Microsoft stopped support of SQL Server 2008R2 this month.

So the only survived versions are SQL 2012 and SQL 2014 😎

gary.strange-sqlconsumer SSCommitted Points: 1816 More actions · Answer 6

create schema String

go

create function String.SingleQuote() returns nchar(1) as Begin return char(39) end

go

alter function String.QuotedSingleQuote() returns nchar(2) as Begin return replicate(String.SingleQuote(), 2) end

go

select replace('gary''s gary''s', String.SingleQuote(),String.QuotedSingleQuote())

Surely we can find someting more interesting to talk about than single quotes. Can't say I enjoyed the article, but thanks for contributing.:-)

victor.girling 17919 SSC Veteran Points: 207 More actions · Answer 7

I agree, much tidier.

EXEC ('SELECT ''O'' + CHAR(39) + ''Neal''')

victor.girling 17919 SSC Veteran Points: 207 More actions · Answer 8

Michael R. OBrien Jr (1/2/2013)
I guess I have never been a fan of triple quoting, I usually use CHAR(39) I find it is a lot easier to read for others:
SELECT 'O' + CHAR(39) + 'Neal'
Just a thought, nice article though

I agree, much tidier.

EXEC ('SELECT ''O'' + CHAR(39) + ''Neal''')

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Good topic !!

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

rstone SSCertifiable Points: 6213 More actions · Answer 10

One thing that I find interesting is that the escape character can be specified with a LIKE. I don't see why the same syntax could not apply to a SET or SELECT.

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

rstone SSCertifiable Points: 6213 More actions · Answer 11

This might be fun to try. The number of quotes required is exponential with the nesting level. I used spaces, not tabs, in some places to help format the output. I added the SELECT @sql for higher @Count values because the string gets too long to print.

DECLARE @sql nvarchar(max)

DECLARE @count int = 0

DECLARE @crlf char(2) = char(13) + char(10)

SET @sql = 'PRINT '' NAME: O''''Neil'''

SELECT @sql as [SQL]

RAISERROR ('Count %d: %s %s%s ', 10, 1, 0, @crlf, @sql, @crlf) WITH NOWAIT

EXEC sp_executesql @sql

WHILE @COUNT < 10

BEGIN

SET @Count += 1

SET @sql = '

DECLARE @sql nvarchar(max)

SET @sql = ''' + REPLACE(@sql, '''', '''''') + '''

--PRINT @sql

EXEC sp_executesql @sql

'

SELECT @sql as [SQL]

RAISERROR ('%sCount %d: %s ', 10, 1, @crlf, @Count, @sql) WITH NOWAIT

EXEC sp_executesql @sql

END

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

gregwjohnston Valued Member Points: 58 More actions · Answer 12

I would like to point out that there really is no such thing as a "single quote" in the English language. It's actually always an apostrophe. But convention has allowed it to be pervasively called a "single quote" mark. We only have quotes and apostrophes.

Doesn't matter in the least and I'm not trying to be a grammar Nazi. But as admins and programmers, language and specifically semantics are our life blood and we should strive to be precise.

SQLArnold SSCommitted Points: 1847 More actions · Answer 13

Hello Kenneth, Nice Post/Question.

I like using CHAR(39), help prevent injection. I know there isn't any concern in the answer but I thought I would share it.

DECLARE @topsql NVARCHAR(2000);

SET @topsql = N'

DECLARE @quotedvar nvarchar(100)

DECLARE @sql nvarchar(1000)

SET @quotedvar = ' + CHAR(39) + 'O' + CHAR(39) + CHAR(39) + 'Neil' + CHAR(39) + '

SET @sql = ' + CHAR(39) + 'PRINT ' + CHAR(39) + CHAR(39) + CHAR(39) + ' + REPLACE(@quotedvar,' + CHAR(39) + CHAR(39) + CHAR(39) + CHAR(39) + ',' + CHAR(39) + CHAR(39) + CHAR(39) + CHAR(39) + CHAR(39) + CHAR(39) + ') + ' + CHAR(39) + CHAR(39) + CHAR(39) + CHAR(39) + '

PRINT @sql

EXEC sp_executesql @sql';

PRINT @topsql;

PRINT N'-------';

EXEC sp_executesql @stmt = @topsql;

Thanks for the question.

Tim

The pain of Discipline is far better than the pain of Regret!

Phil Parkin SSC Guru Points: 246985 More actions · Answer 14

gregwjohnston (7/11/2014)
I would like to point out that there really is no such thing as a "single quote" in the English language. It's actually always an apostrophe. But convention has allowed it to be pervasively called a "single quote" mark. We only have quotes and apostrophes.
Doesn't matter in the least and I'm not trying to be a grammar Nazi. But as admins and programmers, language and specifically semantics are our life blood and we should strive to be precise.

Nonsense. Just because you can't directly access it on your keyboard doesn't mean it does not exist.

http://en.m.wikipedia.org/wiki/Quotation_mark

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

odecar Valued Member Points: 62 More actions · Answer 15

you can use quotename to make the input string a valid SQL Server delimited identifier.

DECLARE @quotedvar nvarchar(100)

DECLARE @sql nvarchar(1000)

SET @quotedvar = 'O''Neil'

SET @sql = 'PRINT ' + quotename(@quotedvar,'''') + ''

PRINT @sql

EXEC sp_executesql @sql

Result

PRINT 'O''Neil'

O'Neil