July 11, 2014 at 7:22 am
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]
July 11, 2014 at 7:25 am
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 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 11, 2014 at 7:29 am
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]
July 11, 2014 at 7:31 am
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]
July 11, 2014 at 8:00 am
Microsoft stopped support of SQL Server 2008R2 this month.
So the only survived versions are SQL 2012 and SQL 2014 😎
July 11, 2014 at 8:45 am
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.:-)
July 11, 2014 at 9:32 am
I agree, much tidier.
EXEC ('SELECT ''O'' + CHAR(39) + ''Neal''')
July 11, 2014 at 9:49 am
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''')
July 11, 2014 at 10:31 am
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
July 11, 2014 at 10:36 am
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. 😉
July 11, 2014 at 1:50 pm
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. 😉
July 11, 2014 at 5:15 pm
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.
July 11, 2014 at 6:22 pm
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!
July 12, 2014 at 12:39 am
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2014 at 4:35 am
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
Viewing 15 posts - 46 through 60 (of 73 total)
You must be logged in to reply to this topic. Login to reply