Post reply

behaviour of LEN()

  • July 30, 2011 at 4:35 am

    #390090

    Hi,

    in SQL Server i have tried the below query:

    SELECT LEN('1 ')

    it returns 1.

    I want the length which includes the spaces. i.e 3.

    Is there any other function available for this?

    Thanks.

    Thanks,
    Pandeeswaran

  • July 30, 2011 at 4:40 am

    #1361202

    As clearly documented in Books Online:

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

    Note:

    To return the number of bytes used to represent an expression, use the DATALENGTH function.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 30, 2011 at 12:40 pm

    #1361255

    This is also true for binary values, since LEN seem to convert them to varchar.

    See http://weblogs.sqlteam.com/peterl/archive/2008/07/22/Why-LEN-differs-from-DATALENGTH-when-using-BINARY-data.aspx

    N 56°04'39.16"
    E 12°55'05.25"

  • August 1, 2011 at 3:38 am

    #1361420

    If you determined to use LEN function and want it to return 3 in your case you can do the following:

    select len('1 ' + 'a') - 1

    :w00t::w00t::w00t:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • August 2, 2011 at 7:53 am

    #1362157

    or

    SELECT LEN(REVERSE('1 ')) 😛

    Nigel
    How to post questions

  • August 2, 2011 at 8:29 am

    #1362206

    nigel. (8/2/2011)

    or

    SELECT LEN(REVERSE('1 ')) 😛

    I wanted to put this first, but it has limitation: will not work for string with trailing spaces :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • August 2, 2011 at 8:33 am

    #1362210

    Eugene Elutin (8/2/2011)

    nigel. (8/2/2011)

    or

    SELECT LEN(REVERSE('1 ')) 😛

    I wanted to put this first, but it has limitation: will not work for string with trailing spaces :hehe:

    Ah! Good point

    Nigel
    How to post questions

  • August 2, 2011 at 9:52 am

    #1362288

    You can also do this:

    SELECT LEN(REPLACE('1 ',' ','A'))

  • August 2, 2011 at 11:35 pm

    #1362812

    The following pattern can be useful when you can't be sure whether the input string is Unicode or not:

    DECLARE @string VARCHAR(100) = ''

    DECLARE @string2 NVARCHAR(100) = N''

    SELECT DATALENGTH(@string) / ISNULL(NULLIF(DATALENGTH(LEFT(@string, 1)), 0), 1)

    SELECT DATALENGTH(@string2) / ISNULL(NULLIF(DATALENGTH(LEFT(@string2, 1)), 0), 1)

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 9 posts - 1 through 8 (of 8 total)

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