Formatted output

  • Comments posted to this topic are about the item Formatted output

  • Nice question, thanks.

    A bit curious as why you use DATALENGTH instead of just LEN.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (10/29/2014)


    Nice question, thanks.

    A bit curious as why you use DATALENGTH instead of just LEN.

    With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.

    DECLARE @L TABLE (Txt nvarchar(20));

    INSERT @L(TxT) VALUES(N'ABC ');

    SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L

    DATALENGTH returns 4, LEN returns 3

  • Louis Hillebrand (10/29/2014)


    Koen Verbeeck (10/29/2014)


    Nice question, thanks.

    A bit curious as why you use DATALENGTH instead of just LEN.

    With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.

    DECLARE @L TABLE (Txt nvarchar(20));

    INSERT @L(TxT) VALUES(N'ABC ');

    SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L

    DATALENGTH returns 4, LEN returns 3

    OK, good to know. But with DATALENGTH you have to be extra careful which data types you are using.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Extra careful, as always..

    BTW. Koen, we're not far apart, I'm in Kortenberg.

  • Louis Hillebrand (10/29/2014)


    BTW. Koen, we're not far apart, I'm in Kortenberg.

    That is indeed quite close 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (10/29/2014)


    Louis Hillebrand (10/29/2014)


    Koen Verbeeck (10/29/2014)


    Nice question, thanks.

    A bit curious as why you use DATALENGTH instead of just LEN.

    With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.

    DECLARE @L TABLE (Txt nvarchar(20));

    INSERT @L(TxT) VALUES(N'ABC ');

    SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L

    DATALENGTH returns 4, LEN returns 3

    OK, good to know. But with DATALENGTH you have to be extra careful which data types you are using.

    I prefer STUFF

    SELECT

    stuff(Ms, 1, 1,'')

    FROM

    ( SELECT

    ';' + DepartmentName AS [text()]

    FROM

    Department

    FOR

    XML PATH('')

    ) AS T ( Ms )

  • Carlo Romagnano (10/29/2014)


    I prefer STUFF

    SELECT

    stuff(Ms, 1, 1,'')

    FROM

    ( SELECT

    ';' + DepartmentName AS [text()]

    FROM

    Department

    FOR

    XML PATH('')

    ) AS T ( Ms )

    +1

    It's the one I use as well.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Louis Hillebrand (10/29/2014)


    Koen Verbeeck (10/29/2014)


    Nice question, thanks.

    A bit curious as why you use DATALENGTH instead of just LEN.

    With DATALENGTH you can also use a space as the separator, LEN strips trailing spaces.

    DECLARE @L TABLE (Txt nvarchar(20));

    INSERT @L(TxT) VALUES(N'ABC ');

    SELECTDATALENGTH(Txt) / 2, LEN(Txt) FROM @L

    DATALENGTH returns 4, LEN returns 3

    The reason why uses DATALENGTH is because the column is DEFINED as NVARCHAR() and DATALENGTH counts number of bytes, and also for that reason is why is divided by 2. (1 UNICODE Character = 2 bytes)

    Using LEN does not require to divide by 2 as count number of characters as you well said, so LEN() - 1 = (DATALENGTH() / 2) - 1 on NVARCHAR() datatypes.

    Nice question!

    ** I also prefer STUFF() and concatenate the separator at the beginning rather than the end.

  • This was removed by the editor as SPAM

  • Thanks for a good question. String concatenation and splitting is always a good topic.

  • Thanks for a good question, Anju.

  • SELECT

    SUBSTRING(Ms, 1, DATALENGTH(Ms)/ 2 - 1)

    FROM

    ( SELECT

    DepartmentName + ';' AS [text()]

    FROM

    Department

    FOR

    XML PATH('')

    ) AS T ( Ms )

    The Ms string is 30 characters, 'Finance;HR;Developers;Testers;' but DATALENGTH(Ms) is 60. I'm guessing that [text()] accounts for the difference, but can you explain how this difference occurs?

    I get the usage of text() as described here http://stackoverflow.com/questions/9493732/difference-between-text-and-string but I don't understand how text() operates in this context.

  • Note that DepartmentName is Unicode which means that there are 2 bytes per character, and DATALENGTH(Ms) returns 60 because it returns the number of bytes, not the number of characters, while LEN(Ms) would return 30 because it returns the number of characters whether Ms is Unicode or not. (Also, LEN does not include trailing spaces in the character count.)

    You can see this if you run the following query:

    SELECT

    DepartmentName

    , LEN(DepartmentName)

    , DATALENGTH(DepartmentName)

    FROM Department

  • James_R_Alves (10/29/2014)


    SELECT

    SUBSTRING(Ms, 1, DATALENGTH(Ms)/ 2 - 1)

    FROM

    ( SELECT

    DepartmentName + ';' AS [text()]

    FROM

    Department

    FOR

    XML PATH('')

    ) AS T ( Ms )

    The Ms string is 30 characters, 'Finance;HR;Developers;Testers;' but DATALENGTH(Ms) is 60. I'm guessing that [text()] accounts for the difference, but can you explain how this difference occurs?

    I get the usage of text() as described here http://stackoverflow.com/questions/9493732/difference-between-text-and-string but I don't understand how text() operates in this context.

    [text()] is a misleading alias in this case... Note that is wrapped with [square brackets]

    just run

    SELECT

    DepartmentName + ';' AS [text()]

    FROM

    Department

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

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