Post reply

Quick question about len

  • April 22, 2010 at 9:14 am

    #218156

    I have two databases when I ran below statement it returns different result...can somebody suggest what is the setting that affect the result

    Database 1

    Select LEN('Ather ')

    retrun result 5

    Database 2

    Select LEN('Ather ')

    retrun result 7

  • April 22, 2010 at 9:20 am

    #1156201

    and if you run:

    Select Datalength('Ather') ?

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • April 22, 2010 at 9:23 am

    #1156206

    Datalength works fine...but this is the last option...

    and also I am curious to know that why len is not returning same result...

  • April 22, 2010 at 9:32 am

    #1156226

    try it like this on both:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SELECT LEN('TEST')

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • April 22, 2010 at 9:36 am

    #1156230

    What version and build are you dealing with in both scenarios?

    Len is always supposed to return the result without the spaces (since it trims the trailing spaces). Are you giving us an example - or is that the actual line of code you're issuing?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • April 23, 2010 at 7:32 am

    #1156826

    Ather M (4/22/2010)

    I have two databases when I ran below statement it returns different result...can somebody suggest what is the setting that affect the result

    Database 1

    Select LEN('Ather ')

    retrun result 5

    Database 2

    Select LEN('Ather ')

    retrun result 7

    Do both database exist on same server if not then service pack could be issue.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • April 23, 2010 at 7:37 am

    #1156836

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • April 23, 2010 at 7:13 pm

    #1157205

    Bhuvnesh (4/23/2010)

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    Spot on.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 24, 2010 at 9:58 am

    #1157279

    Jeff Moden (4/23/2010)

    Bhuvnesh (4/23/2010)

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    Spot on.

    Jeff , i didnt get you here ? did i suggest wrong workaround ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • April 24, 2010 at 10:10 am

    #1157282

    Bhuvnesh (4/24/2010)

    Jeff Moden (4/23/2010)

    Bhuvnesh (4/23/2010)

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    Spot on.

    Jeff , i didnt get you here ? did i suggest wrong workaround ?

    I guess Jeff meant you got the correct root cause of the issue !:-)

  • April 24, 2010 at 12:49 pm

    #1157317

    Bhuvnesh (4/24/2010)

    Jeff Moden (4/23/2010)

    Bhuvnesh (4/23/2010)

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    Spot on.

    Jeff , i didnt get you here ? did i suggest wrong workaround ?

    No... "Spot on" is another way of saying that "I think you hit the nail on the head" and that I agree with your post without further qualifiers.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 28, 2010 at 8:59 am

    #1159015

    Jeff Moden (4/23/2010)

    Bhuvnesh (4/23/2010)

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    Spot on.

    How is len going to be affected by either of those settings? I thought about those, and tried various settings to see if I could get LEN to change, but no combination of these settings changes the behavior described in the LEN function in BOL (i.e. trim the spaces off of the end column and THEN figure out how long it is).

    Did I miss a combo?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • April 28, 2010 at 10:08 am

    #1159104

    Matt Miller (#4) (4/28/2010)

    Jeff Moden (4/23/2010)

    Bhuvnesh (4/23/2010)

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    Spot on.

    How is len going to be affected by either of those settings? I thought about those, and tried various settings to see if I could get LEN to change, but no combination of these settings changes the behavior described in the LEN function in BOL (i.e. trim the spaces off of the end column and THEN figure out how long it is).

    Did I miss a combo?

    Are you selecting from a table? If so, then you need to validate what the settings were when the column was created. You can set the ANSI_PADDING settings before creating a column and the column will be defined that way. Create the table differently on another system - and you will see these kinds of issues.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • April 28, 2010 at 1:47 pm

    #1159264

    Jeffrey Williams-493691 (4/28/2010)

    Matt Miller (#4) (4/28/2010)

    Jeff Moden (4/23/2010)

    Bhuvnesh (4/23/2010)

    Also check ANSI_NULL and ANSI_PADDING settings on both databases ?

    Spot on.

    How is len going to be affected by either of those settings? I thought about those, and tried various settings to see if I could get LEN to change, but no combination of these settings changes the behavior described in the LEN function in BOL (i.e. trim the spaces off of the end column and THEN figure out how long it is).

    Did I miss a combo?

    Are you selecting from a table? If so, then you need to validate what the settings were when the column was created. You can set the ANSI_PADDING settings before creating a column and the column will be defined that way. Create the table differently on another system - and you will see these kinds of issues.

    I just finished trying just that with every combination of ansi_nulls and ansi_padding, creating a table and checking len. it's never counting spaces when they're at the end.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 1, 2010 at 5:35 pm

    #1160938

    Absolutely my bad. Not sure why i was thinking that LEN would be affected by those. What I was thinking of was how much space was wasted in one particular database by using the wrong settings on a particular column and how LEN [font="Arial Black"]didn't [/font]find that problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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