October 5, 2018 at 7:42 am
Lynn Pettis - Friday, October 5, 2018 7:27 AMSean Lange - Friday, October 5, 2018 7:20 AMedwardwill - Friday, October 5, 2018 1:58 AMpatrickmcginnis59 10839 - Thursday, October 4, 2018 6:12 PMSteve Jones - SSC Editor - Thursday, October 4, 2018 3:15 PMpatrickmcginnis59 10839 - Thursday, October 4, 2018 12:58 PMSince you're already mad, can we discuss your work here at SSC?
I am unsure of if you're addressing this to me or what you mean.
It LOOKS like you called him a jerk for keeping score of QOTD gaffs but thats only going to make you the bad guy in this one, number one, you can't post clean editorial content in at least one of three consecutive attempts, number two, you called your customer a jerk.
For all I know, you didn't actually flub editorial content in three consecutive attempts but I as a now confused consumer of SSC content really can't tell, the dumpster fire that this thread has become is just leaving me uninformed on the matter.
As I am the idiot who started the dumpster fire, it may interest some of you to know that I had a very bad night on account of guilt at having been so disobliging about what is a free and fun service, provided by a busy professional. I have no valid defence of being a total numpty, save that I've been suffering from toothache for about two months (it's basically not curable, but enough of that) and occasionally it gets the better of me. I salute Mr Jones for his sterling work, and I will confine myself to a) trying to answer the QotD (got today's correct - yay!) and b) asking questions on the forum and/or finding answers to questions on the forum.
On the contrary you are clearly NOT an idiot. Just a guy who vented a little bit and got his wrist slapped. Keep in mind we are all human (even Steve) and sometimes things don't come across as intended. I was somewhat surprised that Steve made a comment at all. That seems like the kind of thing her normally just ignores. But there are times that some things just rub you a certain way and you have to respond. I say we clear the air and have a virtual Friday beer together. First round is on me.
I'll be honest, don't make excuses just admit it was a mistake and apologize. Then have another virtual beer. I tend to prefer Dr. Pepper or Whiskey.
Guinness or Irish whiskey for me please. Even better with both.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2018 at 9:10 am
In hindsight, I should not have posted publicly and I apologize for that. I had ignored a few in a row, but was rather annoyed myself at the comment that wasn't germane to the question.
October 5, 2018 at 10:03 am
Steve Jones - SSC Editor - Friday, October 5, 2018 9:10 AMIn hindsight, I should not have posted publicly and I apologize for that. I had ignored a few in a row, but was rather annoyed myself at the comment that wasn't germane to the question.
I had my annual review today and I was told that I'm too spiky and defensive! I guess that doesn't exactly come as news around here.
Memo to self. Must. Do. Better.
Thanks for not banning me.
October 8, 2018 at 10:51 am
timwell - Thursday, October 4, 2018 7:45 AMdavid.gugg - Thursday, October 4, 2018 6:51 AMI would like to see how an invalid identifier causes an implicit conversion, as the question was phrased.It seems the explanation of the answer is a bit off as well. The documentation says QUOTENAME returns null if the unicode string is too long or if the quote character is invalid. Nothing about whether the string is a valid identifier.
I have a question: By quoting a string does it make anything a valid identifier?
Select 'A' as [*#($*&(#$&(*@&#$]
returns:*#($*&(#$&(*@&#$
----------------
A
An invalid identifier will cause a parse error, not an implicit conversion. I think the point of the question was that QUOTENAME() was being used to ensure that identifiers that don't conform to the "rules for regular identifiers" don't cause parse errors. And since the output of QUOTENAME is usually part of a concatenation, if the other strings being concatenated, or the variable used to hold the value (if that is what is being done), are not of the same type, then there will need to be implicit conversions to get the datatypes to match.
> By quoting a string does it make anything a valid identifier?
For the most part, yes. Typically, any string you can come up with will be valid if it is delimited (i.e. quoted). There are only two characters that are not allowed, even in delimited identifiers: U+0000 (i.e. NCHAR(0) / NCHAR(0x0000) ) and U+FFFF (i.e. NCHAR(65535) / NCHAR(0xFFFF) ). QUOTENAME recognizes that NCHAR(0) is invalid and returns a NULL. But it doesn't recognize NCHAR(65535) and will pass it back in the quoted / delimited string, but using that will still cause an error. First we can see how NCHAR(0) is handled:
DECLARE @NChar0 [sysname] = N'g' + NCHAR(0) + N'h';
DECLARE @Quoted NVARCHAR(258) = QUOTENAME(@NChar0);
SELECT @NChar0, @Quoted, DATALENGTH(@Quoted), N'DECLARE @T TABLE (' + @Quoted + N' INT);';
EXEC (N'DECLARE @T TABLE (' + @Quoted + N' INT);');
GO
Returns a row showing "g" for the first column (it can't see past the U+0000 character) and NULL for the other 3 columns. It also returns the following error:
Msg 173, Level 15, State 13, Line XXXXX
The definition for column 'INT' must include a data type.
That error is due to a bug that I just found (or it would appear to be a bug since it is not documented behavior and is inconsistent with how string concatenation works in all other cases; reported here: EXECUTE T-SQL string concatenates NULL variable as an empty string instead of making concatenated string NULL ). In most cases QUOTENAME is used to concatenate into a variable that will then be used with EXEC(), and in those cases the variable will be NULL (as expected) and there won't be an error.
Next, we can see how NCHAR(65535) is handled:
DECLARE @NChar65535 [sysname] = N'g' + NCHAR(65535) + N'h';
DECLARE @Quoted NVARCHAR(258) = QUOTENAME(@NChar65535);
SELECT @NChar65535, @Quoted, DATALENGTH(@Quoted), N'DECLARE @T TABLE (' + @Quoted + N' INT);';
EXEC (N'DECLARE @T TABLE (' + @Quoted + N' INT);');
GO
That doesn't returns any NULL columns, but I won't copy and paste into here as the U+FFFF character is hidden and can cause things such as RSS feeds, etc to break if they are not filtering out such things (I learned the hard way 😉 ). So not caught by QUOTENAME(), yet the resulting value still cannot be used as it gets the following error:
Msg 1055, Level 16, State 1, Line XXXXX
'..' is an invalid name because it contains a NULL character or an invalid unicode character.
If you would like to see the exact set of rules for T-SQL identifiers, I have documented them here: Completely Complete List of Rules for T-SQL Identifiers (the official documentation is incomplete and not entirely correct).
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
October 8, 2018 at 11:14 am
Solomon Rutzky - Monday, October 8, 2018 10:51 AMtimwell - Thursday, October 4, 2018 7:45 AMdavid.gugg - Thursday, October 4, 2018 6:51 AMI would like to see how an invalid identifier causes an implicit conversion, as the question was phrased.It seems the explanation of the answer is a bit off as well. The documentation says QUOTENAME returns null if the unicode string is too long or if the quote character is invalid. Nothing about whether the string is a valid identifier.
I have a question: By quoting a string does it make anything a valid identifier?
Select 'A' as [*#($*&(#$&(*@&#$]
returns:*#($*&(#$&(*@&#$
----------------
AAn invalid identifier will cause a parse error, not an implicit conversion. I think the point of the question was that QUOTENAME() was being used to ensure that identifiers that don't conform to the "rules for regular identifiers" don't cause parse errors. And since the output of QUOTENAME is usually part of a concatenation, if the other strings being concatenated, or the variable used to hold the value (if that is what is being done), are not of the same type, then there will need to be implicit conversions to get the datatypes to match.
> By quoting a string does it make anything a valid identifier?
For the most part, yes. Typically, any string you can come up with will be valid if it is delimited (i.e. quoted). There are only two characters that are not allowed, even in delimited identifiers: U+0000 (i.e. NCHAR(0) / NCHAR(0x0000) ) and U+FFFF (i.e. NCHAR(65535) / NCHAR(0xFFFF) ). QUOTENAME recognizes that NCHAR(0) is invalid and returns a NULL. But it doesn't recognize NCHAR(65535) and will pass it back in the quoted / delimited string, but using that will still cause an error. First we can see how NCHAR(0) is handled:
DECLARE @NChar0 [sysname] = N'g' + NCHAR(0) + N'h';
DECLARE @Quoted NVARCHAR(258) = QUOTENAME(@NChar0);
SELECT @NChar0, @Quoted, DATALENGTH(@Quoted), N'DECLARE @T TABLE (' + @Quoted + N' INT);';
EXEC (N'DECLARE @T TABLE (' + @Quoted + N' INT);');
GO
Returns a row showing "g" for the first column (it can't see past the U+0000 character) and NULL for the other 3 columns. It also returns the following error:Msg 173, Level 15, State 13, Line XXXXX
The definition for column 'INT' must include a data type.
That error is due to a bug that I just found (or it would appear to be a bug since it is not documented behavior and is inconsistent with how string concatenation works in all other cases; reported here: EXECUTE T-SQL string concatenates NULL variable as an empty string instead of making concatenated string NULL ). In most cases QUOTENAME is used to concatenate into a variable that will then be used with EXEC(), and in those cases the variable will be NULL (as expected) and there won't be an error.Next, we can see how NCHAR(65535) is handled:
DECLARE @NChar65535 [sysname] = N'g' + NCHAR(65535) + N'h';
DECLARE @Quoted NVARCHAR(258) = QUOTENAME(@NChar65535);
SELECT @NChar65535, @Quoted, DATALENGTH(@Quoted), N'DECLARE @T TABLE (' + @Quoted + N' INT);';
EXEC (N'DECLARE @T TABLE (' + @Quoted + N' INT);');
GO
That doesn't returns any NULL columns, but I won't copy and paste into here as the U+FFFF character is hidden and can cause things such as RSS feeds, etc to break if they are not filtering out such things (I learned the hard way 😉 ). So not caught by QUOTENAME(), yet the resulting value still cannot be used as it gets the following error:Msg 1055, Level 16, State 1, Line XXXXX
'..' is an invalid name because it contains a NULL character or an invalid unicode character.
If you would like to see the exact set of rules for T-SQL identifiers, I have documented them here: Completely Complete List of Rules for T-SQL Identifiers (the official documentation is incomplete and not entirely correct).Take care, Solomon...
Fantastic post - thank you Solomon. You've done some heavy lifting there...
October 8, 2018 at 12:03 pm
edwardwill - Monday, October 8, 2018 11:14 AMSolomon Rutzky - Monday, October 8, 2018 10:51 AMtimwell - Thursday, October 4, 2018 7:45 AMdavid.gugg - Thursday, October 4, 2018 6:51 AMI would like to see how an invalid identifier causes an implicit conversion, as the question was phrased.It seems the explanation of the answer is a bit off as well. The documentation says QUOTENAME returns null if the unicode string is too long or if the quote character is invalid. Nothing about whether the string is a valid identifier.
I have a question: By quoting a string does it make anything a valid identifier?
Select 'A' as [*#($*&(#$&(*@&#$]
returns:*#($*&(#$&(*@&#$
----------------
AAn invalid identifier will cause a parse error, not an implicit conversion. I think the point of the question was that QUOTENAME() was being used to ensure that identifiers that don't conform to the "rules for regular identifiers" don't cause parse errors. And since the output of QUOTENAME is usually part of a concatenation, if the other strings being concatenated, or the variable used to hold the value (if that is what is being done), are not of the same type, then there will need to be implicit conversions to get the datatypes to match.
> By quoting a string does it make anything a valid identifier?
For the most part, yes. Typically, any string you can come up with will be valid if it is delimited (i.e. quoted). There are only two characters that are not allowed, even in delimited identifiers: U+0000 (i.e. NCHAR(0) / NCHAR(0x0000) ) and U+FFFF (i.e. NCHAR(65535) / NCHAR(0xFFFF) ). QUOTENAME recognizes that NCHAR(0) is invalid and returns a NULL. But it doesn't recognize NCHAR(65535) and will pass it back in the quoted / delimited string, but using that will still cause an error. First we can see how NCHAR(0) is handled:
DECLARE @NChar0 [sysname] = N'g' + NCHAR(0) + N'h';
DECLARE @Quoted NVARCHAR(258) = QUOTENAME(@NChar0);
SELECT @NChar0, @Quoted, DATALENGTH(@Quoted), N'DECLARE @T TABLE (' + @Quoted + N' INT);';
EXEC (N'DECLARE @T TABLE (' + @Quoted + N' INT);');
GO
Returns a row showing "g" for the first column (it can't see past the U+0000 character) and NULL for the other 3 columns. It also returns the following error:Msg 173, Level 15, State 13, Line XXXXX
The definition for column 'INT' must include a data type.
That error is due to a bug that I just found (or it would appear to be a bug since it is not documented behavior and is inconsistent with how string concatenation works in all other cases; reported here: EXECUTE T-SQL string concatenates NULL variable as an empty string instead of making concatenated string NULL ). In most cases QUOTENAME is used to concatenate into a variable that will then be used with EXEC(), and in those cases the variable will be NULL (as expected) and there won't be an error.Next, we can see how NCHAR(65535) is handled:
DECLARE @NChar65535 [sysname] = N'g' + NCHAR(65535) + N'h';
DECLARE @Quoted NVARCHAR(258) = QUOTENAME(@NChar65535);
SELECT @NChar65535, @Quoted, DATALENGTH(@Quoted), N'DECLARE @T TABLE (' + @Quoted + N' INT);';
EXEC (N'DECLARE @T TABLE (' + @Quoted + N' INT);');
GO
That doesn't returns any NULL columns, but I won't copy and paste into here as the U+FFFF character is hidden and can cause things such as RSS feeds, etc to break if they are not filtering out such things (I learned the hard way 😉 ). So not caught by QUOTENAME(), yet the resulting value still cannot be used as it gets the following error:Msg 1055, Level 16, State 1, Line XXXXX
'..' is an invalid name because it contains a NULL character or an invalid unicode character.
If you would like to see the exact set of rules for T-SQL identifiers, I have documented them here: Completely Complete List of Rules for T-SQL Identifiers (the official documentation is incomplete and not entirely correct).Take care, Solomon...
Fantastic post - thank you Solomon. You've done some heavy lifting there...
Hello, Yes thanks, very interesting explanation. I thought my question got lost in the discussion. I was going to re-post it in today's QUOTENAME QOTD....
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply