The long identifier

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719936

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71818

    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: 5603

    nice simple question for a monday

    thanks

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

  • Budd

    Hall of Fame

    Points: 3769

    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: 2210

    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: 719936

    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: 17298

    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: 2210

    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)  = 'Wolfe­schlegel­stein­hausen­berger­dorff­welche­vor­altern­waren­gewissen­haft­schafers­wessen­schafe­waren­wohl­gepflege­und­sorg'
    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: 719936

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

    Edited the string to remove them.

  • blehnig

    SSCrazy

    Points: 2210

    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 🙂

Viewing 10 posts - 1 through 10 (of 10 total)

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