The long identifier

Question

The long identifier

Steve Jones - SSC Editor

SSC Guru

Points: 736078
More actions
October 14, 2018 at 10:57 pm

#77522

Comments posted to this topic are about the item The long identifier

Viewing 10 posts - 1 through 9 (of 9 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 to start the week on, thanks, Steve

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

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 2

nice simple question for a monday

thanks

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

Budd Hall of Fame Points: 3781 More actions · Answer 3

No, No, No, No........ What is odd about your string that causes a NULL result ??
SELECT @SomeVar produces a different string that this which can be seen in the declaration, why?
not even copy past reveals the source data in the same way that select does.. But this is the result.
Wolfe-schlegel-stein-hausen-berger-dorff-welche-vor-altern-waren-gewissen-haft-schafers-wessen-schafe-waren-wohl-gepflege-und-sorg

blehnig SSCrazy Points: 2890 More actions · Answer 4

I have been reading this blog for the past 7 years and this is the first one that has prompted my to post. Can someone explain please what is going on with this string posted by Steve?
If I hand type out the string and run the query, I get the string surrounded by "{.. }" as I was expecting. It's only when using a copy/paste from the email that the weirdness begins.
Also, if I copy and paste into a format neutral application like Windows Notepad, you can see that the hyphens mentioned by Budd. Odd that SSMS doesn't not reveal this, neither does PowerShell, coincidentally. What voodoo is this please?

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

What is the weirdness? The question contains information that explained a few things.

First, the length is 130 characters. QUOTENAME() only supports 128 characters, which is the limit for identifiers. Therefore a NULL gets returned. If you hand type the string and get something other than NULL, you mistyped the string.

SELECT @SomeVar works fine. There is no strangeness in the string. If you cut/paste from email, I can't control what characters might be embedded in there by your client or the sender.

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

Budd - Monday, October 15, 2018 7:22 AM
No, No, No, No........ What is odd about your string that causes a NULL result ??
SELECT @SomeVar produces a different string that this which can be seen in the declaration, why?
not even copy past reveals the source data in the same way that select does.. But this is the result.
Wolfe-Âschlegel-Âstein-Âhausen-Âberger-Âdorff-Âwelche-Âvor-Âaltern-Âwaren-ÂgewissenÂ-haftÂ-schafers-Âwessen-Âschafe-Âwaren-Âwohl-Âgepflege-Âund-Âsorg

There do seem to be some odd characters hidden in the string. CHAR(173) but it's showing as a hyphen CHAR(45) when you display it. But it's returning NULL because:

Syntax

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

Arguments

'character_string'
Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

https://docs.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql?view=sql-server-2017

I'm just wondering why QUOTENAME returns an nvarchar(258) not a shorter string if that's all it can cope with?

blehnig SSCrazy Points: 2890 More actions · Answer 7

This is what I used to compare @SomeVar is the original from Steve's post, and @SomeVar2 is what I hand type (direct copy paste).
DECLARE @SomeVar VARCHAR(2000) = 'Wolfeschlegelsteinhausenbergerdorffwelchevoralternwarengewissenhaftschaferswessenschafewarenwohlgepflegeundsorg'
DECLARE @SomeVar2 VARCHAR(2000)= 'Wolfeschlegelsteinhausenbergerdorffwelchevoralternwarengewissenhaftschaferswessenschafewarenwohlgepflegeundsorg'

SELECT IIF(@SomeVar = @SomeVar2, 'T','F') -- This returned a "F" for me, even though I can see no difference. It could be that my old eyes are missing something

SELECT QUOTENAME(@SomeVar,'{'),@SomeVar -- this returned the NULL as described in the QOTD, and the string with hyphens
SELECT QUOTENAME(@SomeVar2,'{'),@SomeVar2 -- this returned the string surrounded by the '{' ...'}', and the string without hyphens

* my client is Chrome, (Version 69.0.3497.100 (Official Build) (64-bit)
Note: I am sorry for directing the discussion away from the QOTD, which was interesting. I am just confused by why SSMS and PowerShell are unable to reveal the hidden characters... other than by using a SELECT. In no way is my inquiry meant to be argumentative, only asking for help from those with more knowledge than I.

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

Ah, it does appear some hidden characters are in there, hidden by HTML.

Edited the string to remove them.

blehnig SSCrazy Points: 2890 More actions · Answer 9

I did a little research and it seems like this effect is in part related to the change in SSMS to using the Visual Studio shell, as mentioned in this MSDN forum post from a few years ago: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8c5cbfce-7522-45f9-b3f3-c9e57b42b4b7/ssms-2012-not-displaying-special-ansii-chars-in-the-text-editor-like-ssms-2008-can?forum=sqltools

I had not run into this before, so it surprised me. So, I learned multiple things today. 🙂

...and, with the hidden UNICODE characters removed, the string in only 111 long, and so works with the QUOTENAME... which, with hide sight was probably done on purpose as a QOTD trick 🙂