Post reply

DATALENGTH vs LEN epiphany

  • November 10, 2008 at 9:07 pm

    #202606

    I have always used len to determine the length of the field unknownly without knowning that LEN does not include trailing blanks. See BOL statement below:

    Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks

    I guess my eyes never read the 3 words after the last comma. Go figure. For those out there that need to know the exact length, please use the DATALENGTH function.

  • November 10, 2008 at 9:15 pm

    #896701

    Be careful using Datalength because unicode takes up two spots. The following results in a length of 6.

    Declare @test-2 nvarchar(3)

    Set @test-2 = '123'

    Select Datalength(@test)

  • November 10, 2008 at 9:24 pm

    #896705

    If you need to make sure you don't cut off the trailing spaces, you can concatenate a character to the end and then subtract 1.

    Declare @test-2 nchar(4)

    Set @test-2 = '123 '

    Select Len(@test + '|')-1

    Select Len(@test)

    Select Datalength(@test)

  • November 11, 2008 at 11:24 am

    #897086

    Doh. I didn't even think about that. Thanks for the example.

Viewing 4 posts - 1 through 3 (of 3 total)

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