ok, that probalby is going to be my answer, but I have a required followon.  WHY does that work?  that makes no sense.  that should return either '' or a blank empty string, not a single quote.

Is this a special rule built into the T-SQL interpreter, to forget parsing rules when there are four single quotes together, and return a single quote?  Maybe if you select ''''' it returns the machine name, eh?

grrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr

aneeshattingal, thank you for taking a shot, but I don't think that explains it.  

Seems to me, select '''' should return either '' or empty string.  There is no single quote enclosed by anything.  Why doesn't the interpreter see '''' as two consecutive empty strings?  maybe if I select dogdogdogdog  I will get get dog back?

Here is what would make sense:
select "'"

That is a double quote, followed by a single quote, then closed with a double.

But, for no good reason, T-SQL does not handle double quotes, so we are left with a non-sensible system of single quotes.

This makes me mad.  

ScottPletcher:  My respects and appreciation for your bringing this up.  It looks like QUOTENAME() is a legitimate path.  However, my basic gripe is this should be a total no-brainer process.  I admit to being dim, so it took me reading all the way through this description from MSDN, before I found enough info to say for sure.  This is hardly no-brainer.  


******************************************************************
SQL Server 2005 Books Online (September 2007)
QUOTENAME (Transact-SQL)

Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server 2005 delimited identifier.

QUOTENAME ( 'character_string' [ , 'quote_character' ] )

 Arguments

' character_string '

    Is a string of Unicode character data. character_string is sysname.

' quote_character '

    Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.
http://msdn2.microsoft.com/en-us/library/ms176114.aspx
********************************************************************************************

fesnyng:  Obviously, you are going to score, because you expressed agreement with me, while I am flaming.  Hell, I'll follow you to war now.

On more serious note, thank you for understanding and explaining.  I had been thinking about using the ASCII solution.... and.. of course I have the ASCII character codes memorized, of course.  ** rolling eyes **  Then, you made my day, by posting not only the correct codes, but an example of to embed them in the type of code I am working on!  This makes you the most awesome question answerer I ever saw.  

aneeshattingal:  Your answer was perfect.  You are getting the original 65 points and the graded answer, since you were first, and it worked as soon as I tried it.  Plus, it gets infinite style points for brevity.  Nothing like the short, perfect answer in the heat of hurried situation to improve a day at the office.  Once I had your answer, I knew I could get done what I needed.  I have indulged in this thread as a way of reinforcing whatever I need to learn, so I  never need to suffer this particular hell again, of being confounded by the most basic T-SQL on a short fuse.  I don't mind puzzles, but irrational puzzles when I am short on time are a bummer to me.

ScottPletcher (again).  The O'Brien is a great example for lots of purposes.  My compliments again, for providing this tidbit.  I'll remember O'Brien.

Back to quotename, I noticed the default delimiter for strings is square bracket.  The problem with any delimiter is the string might contain the delimiter, no matter what it is.  I suppose you could avoid the problem by delimiting strings with something that is not a string, or a string that would be guaranteed excluded from the string.  These rules would allow unambiguous processing of delimited strings.  

If a string can include the string-delimiter, this problem always has potential to exist.

Picking ' as the default string delimter is retarded, since ' is a common string value in English.  Same can be said for square brackets, though to a lesser extent.  Maybe this is why unix people use the ` , which I never really have understood, except as an interference in my Windows-based activity.  

I guess among all the options I can think of, '''' is as good as any.  It's short, easy to remember.  If we added a special new character that somehow could not be in any string, adoption might be a bummer, with the payoff being that only people like us have slightly easier work lives.

Here is the thing though... I do this sort of programming with PHP/MySQL all the time, and I never have this sort of problem.  There is something odd about the T-SQL interpreter.  It would take me awhile to reproduce, but I saw it when I was going through a matrix of syntax variations trying to find one that would work.  T-SQL could see either @FieldValue or Agriculture, but not 'Agriculture'   I never have this problem in PHP/MySQL.

I have a feeling the best info on this issue lies in understand how Unix does certain things better than Windows at this string parsing level.

fesnyng:  I have a feeling your comments are 100% on the money.  If quotename() is t-sql's answer for how things work in php/mysql, then I don't understand why I never have been confused by php/mysql, but T-sql repeatedly proves 100% effective, but requires weird functions and strings, with the letter N thrown in oddly for good measure.  If I am misguided on this, it's worth points for someone to help me see that.


POINTS ON THE BOARD

aneeshattingal: 65 points, with gold star for perfect brevity and elegance in responding

ScottPletcher: 85 points for agreeing with me and providing the best solution available today

fesnyng:  50 points for putting php on the table

still available:  50 points for explaining how/why t-sql is stupid compared to php/javascript/mysql.

potential bonus:  50 bonus points if answer to above question is stupendously awesome, as has been the case in this thread already.

I think the edge held by php, allowing it to avoid this problem, is it allows either single or double quotation marks.  With nesting, you always can tell the interpreter what you want in a logical manner.  It is not always simple, but it logical, and definitely simpler than some of the gymnastics I have been forced into by t-sql over the single quote issue.  Example, shows complications better than this question, is at:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23179246.html  

The same coding in php is totally simple compared this
exec sp_executesql @sql, N'@res INT OUTPUT' , @LookupKeyFieldValue OUTPUT  

I look at that and say,,,, ok, whatever.  What I had before was a lot simpler, and works just fine, except in the case where the @sql string has to be comprised of values read from variables... for no good reason, using variables to build the @SQL leads to use of a strange looking function that says output twice.  On top of that, random string delimiter rules complicate further.

Why doesn't t-sql allow use of double quotes as a string delimiter?

CORRECTION (had wrong name on this):
ScottPletcher:  Obviously, you are going to score, because you expressed agreement with me, while I am flaming.  Hell, I'll follow you to war now.

On more serious note, thank you for understanding and explaining.  I had been thinking about using the ASCII solution.... and.. of course I have the ASCII character codes memorized, of course.  ** rolling eyes **  Then, you made my day, by posting not only the correct codes, but an example of to embed them in the type of code I am working on!  This makes you the most awesome question answerer I ever saw.  

Points are correct.
POINTS ON THE BOARD

aneeshattingal: 65 points, with gold star for perfect brevity and elegance in responding

ScottPletcher: 85 points for agreeing with me and providing the best solution available today

fesnyng:  50 points for putting php on the table

still available:  50 points for explaining how/why t-sql is stupid compared to php/javascript/mysql.

potential bonus:  50 bonus points if answer to above question is stupendously awesome, as has been the case in this thread already.

POINTS ON THE BOARD

aneeshattingal: 65 points, with gold star for perfect brevity and elegance in responding

fesnyng: 85 points for agreeing with me and providing the best solution available today <-- I kept mixing up who said this, because scott quoted fesnyng.

fesnyng:  50 points for putting php on the table

Scott:  20  points for quotename(), the O'Brien example

fesnyng:  30 points for being a good sport when I kept goofing up credit.