Formatted output

  • Anju Renjith

    Ten Centuries

    Points: 1135

    Comments posted to this topic are about the item Formatted output

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    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

  • Louis Hillebrand

    Hall of Fame

    Points: 3990

    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 ');

    SELECT DATALENGTH(Txt) / 2, LEN(Txt) FROM @L

    DATALENGTH returns 4, LEN returns 3

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    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 ');

    SELECT DATALENGTH(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

  • Louis Hillebrand

    Hall of Fame

    Points: 3990

    Extra careful, as always..

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

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21794

    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 ');

    SELECT DATALENGTH(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 )

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    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

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    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 ');

    SELECT DATALENGTH(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

  • Ed Wagner

    SSC Guru

    Points: 286958

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

  • stephen.long.1

    SSCrazy

    Points: 2568

    Thanks for a good question, Anju.

  • James_R_Alves

    SSC Veteran

    Points: 259

    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.

  • briankwartler

    Ten Centuries

    Points: 1332

    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

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    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