QUOTENAME Problem

I have following four cases

Code Snippet

1) select QUOTENAME (QUOTENAME ('ABCD', ''''),'''' )
Output: '''ABCD'''

Code Snippet

2) select QUOTENAME (QUOTENAME ('ABCD', ''''),']' )
Output: ['ABCD']

Code Snippet

3) select QUOTENAME (QUOTENAME ('ABCD', ''''),'"' )
Output: "'ABCD'"

Code Snippet

4) select QUOTENAME (QUOTENAME ('ABCD', '"'),'"' )
Output: """ABCD"""

Now my questions is second & three outputs are fine, but what happen to the first & forth one. I want single quote twice around string(in first case) and double quote twice around string(in forth case) but it gives me three single/double quotes, WHY ?

Gurpreet S. Gill

There was a recent post here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1537605&SiteID=1

In which I noted this behavior in Louis Davidson's example. This behavior also appears in books online so it is intended and you will need to account for it in your coding.

|||

Sorry, but I can not see what your concern is. Let me try to explain 1 and 4.

1 - select QUOTENAME (QUOTENAME ('ABCD', ''''),'''' )

The first call to the function will produce 'ABCD', correct?. Ther second call has to wrap the string 'ABCD' between aposthopes (including the existing aposthropes), so in order to wrap an aposthrope between apostropes you have to double the inner apostrophe. The result of the second call will be '''ABCD''', where the inner apostrophes were double. Let us put some spaces to differentiate them.

' ''ABCD'' '

2 - select QUOTENAME (QUOTENAME ('ABCD', '"'),'"' )

The same as in number 1, but this time both wrap will be between the double qoute ("). The inner double quote should be double and wrap them between double quote.

" ""ABCD"" "

We need to scape the character inside the string and we do it doubling the character being used, in those cases apostrophe and double quote. Let us see what happend if we decide to do the same with ']'.

select quotename(quotename('ABCD', ']'), ']')

Result:

[[ABCD]]]

The behavior with ']' is different compare with apostrophe or double quote, just the most right is doubled.

Hope my english does not get you dizzy.

AMB