QUOTENAME() Types

Question

QUOTENAME() Types

Steve Jones - SSC Editor

SSC Guru

Points: 736078
More actions
October 3, 2018 at 11:13 pm

#77521

Comments posted to this topic are about the item QUOTENAME() Types

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72619 More actions · Answer 1

Nice question, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Toreador SSChampion Points: 11365 More actions · Answer 2

I don't think that's the right answer. It always returns nvarchar - but if the input is invalid then the nvarchar value is NULL
If you run
select QUOTENAME('x',130) x into temptable
and then examine the definition of temptable you will see that column x is nvarchar(258)

Jonathan AC Roberts SSCoach Points: 18334 More actions · Answer 3

Toreador - Thursday, October 4, 2018 4:28 AM
I don't think that's the right answer. It always returns nvarchar - but if the input is invalid then the nvarchar value is NULL
If you run
select QUOTENAME('x',130) x into temptable
and then examine the definition of temptable you will see that column x is nvarchar(258)

Just out of interest if you run:
select NULL x into temptable
It creates an int column.

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 4

nice question

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

david.gugg SSCertifiable Points: 6090 More actions · Answer 5

Toreador - Thursday, October 4, 2018 4:28 AM
I don't think that's the right answer. It always returns nvarchar - but if the input is invalid then the nvarchar value is NULL
If you run
select QUOTENAME('x',130) x into temptable
and then examine the definition of temptable you will see that column x is nvarchar(258)

I would like to see how an invalid identifier causes an implicit conversion, as the question was phrased.

Be still, and know that I am God - Psalm 46:10

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

The question was "If I use QUOTENAME(), what is the type of data that is returned to me?". The answer is NVARCHAR(258).

If the arguments are invalid the VALUE returned may be NULL, but that is not a data type.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Yeah the NULL is still nvarchar(128), it doesn't suddenly lose its datatype. The "correct" answer is not right. Good question though.

_______________________________________________________________

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/

timwell SSCertifiable Points: 5315 More actions · Answer 8

david.gugg - Thursday, October 4, 2018 6:51 AM
Toreador - Thursday, October 4, 2018 4:28 AM
I don't think that's the right answer. It always returns nvarchar - but if the input is invalid then the nvarchar value is NULL
If you run
select QUOTENAME('x',130) x into temptable
and then examine the definition of temptable you will see that column x is nvarchar(258)
I 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

edwardwill SSCertifiable Points: 5845 More actions · Answer 9

Scott Coleman - Thursday, October 4, 2018 7:16 AM
The question was "If I use QUOTENAME(), what is the type of data that is returned to me?". The answer is NVARCHAR(258).
If the arguments are invalid the VALUE returned may be NULL, but that is not a data type.

Three days on the bounce. Going for a record of borked QotD?

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 10

edwardwill - Thursday, October 4, 2018 8:28 AM
Three days on the bounce. Going for a record of borked QotD?

If your intention is to be a jerk, you are succeeding.

If you don't have anything to relate to the question, perhaps you could refrain from posting, or contribute your own questions.

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 11

The wording of the question was a bit misleading. I should not have conflated a value with a type, as NULL doesn't have a type, but conforms to the type that it is replacing.

Question corrected.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Editor: Quote redacted

I really hope that isn't the way you talk to others in a professional environment. Please refrain from using foul or abuse language. We do try to keep this site professional (doesn't always work).
Also, some of us don't take the QotD seriously. It tests our knowledge and we sometimes get them wrong. The question may be poorly worded, or the explanation may not make sense. It isn't anything to get upset about.

edwardwill SSCertifiable Points: 5845 More actions · Answer 13

Lynn Pettis - Thursday, October 4, 2018 8:51 AM
I really hope that isn't the way you talk to others in a professional environment. Please refrain from using foul or abuse language. We do try to keep this site professional (doesn't always work).
Also, some of us don't take the QotD seriously. It tests our knowledge and we sometimes get them wrong. The question may be poorly worded, or the explanation may not make sense. It isn't anything to get upset about.

That's how everyone in my "professional" environment talks - our CEO recently said, at a company meeting, "Thank **** we're a sweary company". We're grown-ups, and that's how all the grown-ups I know talk (I'm 61 years old and counting). I work with some seriously talented individuals - developers, DBAs &c - who aren't in the least bothered by the odd swear though they do get seriously narked if the code isn't up to qualtiy. But hey, I'm a guest here so I promise not to swear again. And I also promise not to criticise QotD until I've submitted my own (I'd expect to be skating in Hades first, but never say never).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

edwardwill - Thursday, October 4, 2018 8:57 AM
That's how everyone in my "professional" environment talks - our CEO recently said, at a company meeting, "Thank **** we're a sweary company". We're grown-ups, and that's how all the grown-ups I know talk (I'm 61 years old and counting). I work with some seriously talented individuals - developers, DBAs &c - who aren't in the least bothered by the odd swear though they do get seriously narked if the code isn't up to qualtiy. But hey, I'm a guest here so I promise not to swear again. And I also promise not to criticise QotD until I've submitted my own (I'd expect to be skating in Hades first, but never say never).

I will admit that I curse, even at work and usually under my breath and because I am frustrated with something I am working on at the time. The difference is that I don't do it during professional conversations. It is simply inappropriate and totally uncalled for in that environment. Over the years I have found that I simply don't appreciate that kind of language especially on a constant basis.