QUOTESTRING behavior
-
I was playing around with QUOTESTRING yesterday and noticed some odd behavior. I wondered what would happen if the string passed to it was already quoted. The most robust behavior would be to return the string unchanged, but that was not it. However, it acted oddly.
Original String Result tablename [tablename] [tablename [[tablename] tablename] [tablename]]] [tablename] [[tablename]]] [tablename]]]] [[tablename]]]]]]]]] [[tablename]] [[[tablename]]]]] ]table]name []]table]]name] Notice that left or opening brackets cause no problems -- they are ignored. An opening bracket is just appended to the beginning of the string. However, closing/right brackets seem to cause QUOTENAME to react. It doesn't just add a right bracket to the end but repeats the number of existing right brackets again then adds another. So if the string terminates with 3 closing brackets (']]]') the result will have 7 closing brackets (']]]]]]]'), the original 3, the original 3 duplicated, and then the one added to match the opening bracket appended to the beginning of the string. (Actually, it seems to be 3 sets of original/duplicate pairs.)
The most interesting is that QUOTENAME seems to be scanning the string and duplicating a right/closing bracket wherever it is in the string, even at the beginning -- as shown in the last example.
It's a minor thing, really. I just have not seen this documented anywhere. I only have access to SS2k, could someone check to see if this is the same in SS2005?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
It is adding an escape character so that the original ] in the string is still treated as a character in the string. If you read BOL and think you'll understand.
-
Thanks, I see that now and it makes sense (in some contexts).
I was just working on a proc that gets in table and column names and I wanted to make sure it worked if a name came in already quoted. So I can't just blindly call QUOTESTRING and expect it to cover for me - alas.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Of course, you can still fix it...
QUOTENAME(REPLACE(REPLACE(somecolumn,']',''),'[',''))
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, I can't do that because it would remove every bracket in the string. I just verified, I can CREATE TABLE [dbo].[some]]name](...) and it creates a table named 'some]name'! I think I have an old memory that this was possible but I have never worked anywhere that allowed special characters as part of a database object name -- with the exception of '#' (for 'number') as part of a column name.
I solved the problem by specifying in the documentation that "schema and table names cannot be quoted."
This is why I have avoided this DBA stuff for all these years. Give me a fifteen-table join any day...
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Heh... yeah, some naming standards seem appropriate. Don't let them use blanks in their names, either.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply