LEN()

  • Primo Dang

    SSCrazy

    Points: 2643

    L' Eomot Inversé (3/29/2013)


    Primo Dang (3/29/2013)


    I'm glad so many people enjoyed it, thank you! =]

    I decided to submit this question when I saw this question from 2010[/url], because some people mentioned there should be an option involving leading spaces.

    That seems to be the wrong link - today's question, not a 2010 one.

    Are you sure? The name is the same and the question is very similar, but I tried clicking the link and it was the old one. Here's the URL: http://www.sqlservercentral.com/questions/LEN/69562/[/url]

    Edit: included actual link on the URL text.

  • TomThomson

    SSC Guru

    Points: 104765

    Primo Dang (3/29/2013)


    L' Eomot Inversé (3/29/2013)


    Primo Dang (3/29/2013)


    I'm glad so many people enjoyed it, thank you! =]

    I decided to submit this question when I saw this question from 2010[/url], because some people mentioned there should be an option involving leading spaces.

    That seems to be the wrong link - today's question, not a 2010 one.

    Are you sure? The name is the same and the question is very similar, but I tried clicking the link and it was the old one. Here's the URL: http://www.sqlservercentral.com/questions/LEN/69562/[/url]

    Edit: included actual link on the URL text.

    No, I was wrong. Careless of me. :blush:

    I mistakenly looked at the question list on the page before checking the question text, but apparently a link to the old question delivers a page with the old question above todays list of recent questions. Positively weird :crazy:!

    Tom

  • Primo Dang

    SSCrazy

    Points: 2643

    Yeah, the question list defaults to the latest questions, no matter to which question you specifically link.

    I actually have a lot of trouble answering old questions because of that. Every day, I have to change the questions per page to 100 and navigate back page by (agonizing, to borrow from Jeff Moden) page to the ones I haven't answered. =P

  • Bangla

    Hall of Fame

    Points: 3137

    "...excluding trailing spaces...." is the key to the answer...Nice question....

  • Dineshbabu

    Hall of Fame

    Points: 3220

    I'm not aware of that LEN() willl ignore trailing spaces. Thanks for making me to learn..

    If somebody explains the difference b/w Datalength() & Len() in this thread will be useful.

    --
    Dineshbabu
    Desire to learn new things..

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Dineshbabu (4/1/2013)


    If somebody explains the difference b/w Datalength() & Len() in this thread will be useful.

    Datalength returns the amount of bytes that would be used on disk for storing the data in uncompressed form (excluding the two bytes overhead for varying length data). For varchar, that is the actual number of characters, including trailing spaces. For nvarchar, it is twice that, because each unicode character takes up two bytes.

    For char, datalength always returns the declared length, because char values are always padded up to the specified maximum length. And for nchar, that is agaim multiplied by two.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Miles Neale

    SSChampion

    Points: 13147

    Nice question.

    Thanks Hugo for your work as well.

    M.

    Not all gray hairs are Dinosaurs!

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Thanks Hugo,

    I have one more doubt, will ANSI_PADDING set option affects the datastorage?

    --
    Dineshbabu
    Desire to learn new things..

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Dineshbabu (4/1/2013)


    Thanks Hugo,

    I have one more doubt, will ANSI_PADDING set option affects the datastorage?

    No and yes.

    No, because the working of LEN and DATA_LENGTH are not affected by this option, they still work exactly the same.

    Yes, because as a result of indirect effects, the results can still be different.

    Here's a repro:

    SET ANSI_PADDING OFF;

    CREATE TABLE dbo.Test

    (c char(10), nc nchar(10), vc varchar(10), nvc nvarchar(10));

    INSERT INTO dbo.Test(c, nc, vc, nvc)

    VALUES ('abc ', N'abc ', 'abc ', N'abc ');

    SELECT LEN(c), LEN(nc), LEN(vc), LEN(nvc)

    FROM dbo.Test;

    SELECT DATALENGTH(c), DATALENGTH(nc), DATALENGTH(vc), DATALENGTH(nvc)

    FROM dbo.Test;

    go

    DROP TABLE dbo.Test;

    go

    Run this twice, with the ANSI_PADDING option ON and OFF. As you see, the DATALENGTH of the vc column is affected by the option. But not because the function behaviour is changed by the option - the change is in the INSERT statement. Here, the trailing spaces will be removed from the varchar column when the option is off. Since the trailing spaces are removed, only the letters abc are stored, and DATALENGTH correctly reports three bytes used.

    Some important warnings:

    1. ANSI_PADDING affects varchar columns, but not nvarchar columns. And varchar(max) columns are also excluded.

    2. ANSI_PADDING only affects data that is stored in a table. When assigning values to a variable, this option has no effect.

    3. ANSI_PADDING is a deprecated option. Both the option and the ANSI_PADDING OFF behaviour will be removed in a future version of SQL Server. Don't use this option in new code, and replace it if it's still being used in existing code.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • kapil_kk

    SSC-Insane

    Points: 21316

    Hugo Kornelis (4/2/2013)


    Dineshbabu (4/1/2013)


    Thanks Hugo,

    I have one more doubt, will ANSI_PADDING set option affects the datastorage?

    No and yes.

    No, because the working of LEN and DATA_LENGTH are not affected by this option, they still work exactly the same.

    Yes, because as a result of indirect effects, the results can still be different.

    Here's a repro:

    SET ANSI_PADDING OFF;

    CREATE TABLE dbo.Test

    (c char(10), nc nchar(10), vc varchar(10), nvc nvarchar(10));

    INSERT INTO dbo.Test(c, nc, vc, nvc)

    VALUES ('abc ', N'abc ', 'abc ', N'abc ');

    SELECT LEN(c), LEN(nc), LEN(vc), LEN(nvc)

    FROM dbo.Test;

    SELECT DATALENGTH(c), DATALENGTH(nc), DATALENGTH(vc), DATALENGTH(nvc)

    FROM dbo.Test;

    go

    DROP TABLE dbo.Test;

    go

    Run this twice, with the ANSI_PADDING option ON and OFF. As you see, the DATALENGTH of the vc column is affected by the option. But not because the function behaviour is changed by the option - the change is in the INSERT statement. Here, the trailing spaces will be removed from the varchar column when the option is off. Since the trailing spaces are removed, only the letters abc are stored, and DATALENGTH correctly reports three bytes used.

    Some important warnings:

    1. ANSI_PADDING affects varchar columns, but not nvarchar columns. And varchar(max) columns are also excluded.

    2. ANSI_PADDING only affects data that is stored in a table. When assigning values to a variable, this option has no effect.

    3. ANSI_PADDING is a deprecated option. Both the option and the ANSI_PADDING OFF behaviour will be removed in a future version of SQL Server. Don't use this option in new code, and replace it if it's still being used in existing code.

    gr88 Explanation Hugo 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • This was removed by the editor as SPAM

  • Bangla

    Hall of Fame

    Points: 3137

    Thanks Hugo, learn something..

  • manik_anu

    SSCrazy

    Points: 2367

    Hugo Kornelis (4/1/2013)


    Dineshbabu (4/1/2013)


    If somebody explains the difference b/w Datalength() & Len() in this thread will be useful.

    Datalength returns the amount of bytes that would be used on disk for storing the data in uncompressed form (excluding the two bytes overhead for varying length data). For varchar, that is the actual number of characters, including trailing spaces. For nvarchar, it is twice that, because each unicode character takes up two bytes.

    For char, datalength always returns the declared length, because char values are always padded up to the specified maximum length. And for nchar, that is agaim multiplied by two.

    nice one... thanks hugo....

    ****manik****

    Manik
    You cannot get to the top by sitting on your bottom.

  • manik_anu

    SSCrazy

    Points: 2367

    Hugo Kornelis (4/2/2013)


    Dineshbabu (4/1/2013)


    Thanks Hugo,

    I have one more doubt, will ANSI_PADDING set option affects the datastorage?

    No and yes.

    No, because the working of LEN and DATA_LENGTH are not affected by this option, they still work exactly the same.

    Yes, because as a result of indirect effects, the results can still be different.

    Here's a repro:

    SET ANSI_PADDING OFF;

    CREATE TABLE dbo.Test

    (c char(10), nc nchar(10), vc varchar(10), nvc nvarchar(10));

    INSERT INTO dbo.Test(c, nc, vc, nvc)

    VALUES ('abc ', N'abc ', 'abc ', N'abc ');

    SELECT LEN(c), LEN(nc), LEN(vc), LEN(nvc)

    FROM dbo.Test;

    SELECT DATALENGTH(c), DATALENGTH(nc), DATALENGTH(vc), DATALENGTH(nvc)

    FROM dbo.Test;

    go

    DROP TABLE dbo.Test;

    go

    Run this twice, with the ANSI_PADDING option ON and OFF. As you see, the DATALENGTH of the vc column is affected by the option. But not because the function behaviour is changed by the option - the change is in the INSERT statement. Here, the trailing spaces will be removed from the varchar column when the option is off. Since the trailing spaces are removed, only the letters abc are stored, and DATALENGTH correctly reports three bytes used.

    Some important warnings:

    1. ANSI_PADDING affects varchar columns, but not nvarchar columns. And varchar(max) columns are also excluded.

    2. ANSI_PADDING only affects data that is stored in a table. When assigning values to a variable, this option has no effect.

    3. ANSI_PADDING is a deprecated option. Both the option and the ANSI_PADDING OFF behaviour will be removed in a future version of SQL Server. Don't use this option in new code, and replace it if it's still being used in existing code.

    wow... superb explanation... thanks dinesh, hugo.....cool....;-)

    Manik
    You cannot get to the top by sitting on your bottom.

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Thanks for your detailed explaination Hugo. I can find definitions for each and every function seperately in BOL. But i can't find such relative explainations over there.

    --
    Dineshbabu
    Desire to learn new things..

Viewing 15 posts - 16 through 30 (of 34 total)

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