Tricky ...VARCHAR

  • CREATE

    TABLE #TEST(A varchar(40),B varchar(30));

    INSERT

    INTO #TEST(A) VALUES('A');

    INSERT

    INTO #TEST(B) VALUES('Some text');

    INSERT

    INTO #TEST(A) VALUES('B');

    INSERT

    INTO #TEST(B) VALUES('Some more text');

    ------------------------------------------------------------

    CREATE

    TABLE #TEST2(A varchar,B varchar(30));

    INSERT

    INTO #TEST2(A) VALUES('A');

    INSERT

    INTO #TEST2(B) VALUES('Some text');

    INSERT

    INTO #TEST2(A) VALUES('B');

    INSERT

    INTO #TEST2(B) VALUES('Some more text');

    Q1:

    SELECT

    COUNT(*)FROM #TEST GROUP BY LEN(ISNULL(B,A));

    go

    Q2:

    SELECT

    COUNT(*) from #TEST GROUP BY LEN(ISNULL(A,B));

    Q3:

    SELECT

    COUNT(*)FROM #TEST2 GROUP BY LEN(ISNULL(B,A));

    go

    Q4:

    SELECT

    COUNT(*) from #TEST2 GROUP BY LEN(ISNULL(A,B));

    if you see results of above query the functionality of LEN(ISNULL(B,A)) changes

    just because ini #test2 i m declaring A as varchar instead of varchar(40)

    Can any body explain WHY ???

    i m apolgise that i m re-posting this Question:):)

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

  • Already asked in the appropriate Question of the Day thread

    http://www.sqlservercentral.com/Forums/Topic565885-1370-1.aspx

    Please don't post multiple threads with the same question. It wastes people's time, it breaks replies up and results in people answering already answered questions.

    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
  • Its because of ISNULL() function. ISNULL(exp1, expr2) always returns expr1 if expr1 is not null. Else expr2 typecasted to expr1. As column 'A' is of varchar with no length.. so default 1 character in length. When evaluating ISNULL(), when column A is null, it type casted value of columnB to 1 char i.e., trimmed rest of characters.. As column 'A' contained values "A" and "B".. so len(ISNULL(A,B)) returned 1 each. When ColumnA is NULL and columnB contained values 'Some text' and 'some more text'... it has taken first character because of expr1 in ISNULL(). So len(ISNULL(A,B)) again returned 1 each. Alltogether there are 4 1's. So when GROUP BY is applied, COUNT(*) gave you 4. 🙂

  • Please note: 3 year old thread.

    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

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

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