Type lengths

  • Comments posted to this topic are about the item Type lengths

  • Good Question!

    The information in the MSDN link is hard to grasp in my opinion (it is a long way from reading "must be implicitly convertible" to understanding that the conversion may result in loss of data), but the example provided makes it very clear.

    Thanks for this QOTD!

    Best Regards,

    Chris Büttner

  • Nice one!

    Coalesce is a pretty useful function - it comes in handy when dealing with nulls....

  • Good question! I had to do some investigation of coalesce which looks like a very useful function.

  • Did I miss something? If two columns are 1 in lengh, and the others are 9 "some text" and 14 "Some more text", then would not the count(*) give you 2 (1 length) 1(9 lenght) and 1(14 lenght).

    I did this on my database and i got

    Query1

    -----------

    2

    1

    1

    Query2

    -----------

    4

    Query3

    -----------

    2

    1

    1

    Confused - - - as normal

    Doug

  • douglascfast (9/9/2008)


    Did I miss something? If two columns are 1 in lengh, and the others are 9 "some text" and 14 "Some more text", then would not the count(*) give you 2 (1 length) 1(9 lenght) and 1(14 lenght).

    I did this on my database and i got

    Query1

    -----------

    2

    1

    1

    Query2

    -----------

    4

    Query3

    -----------

    2

    1

    1

    Confused - - - as normal

    Doug

    I also got the same results. Anything missing?

  • Yes, that's 3 rows, 1 row, 3 rows...

  • Hi Doug,

    thats exactly the purpose of the question.

    If you execute SELECT ISNULL(A,B) FROM #Testyou will get four 1-char strings because of the implicit conversion.

    A

    S

    B

    S

    Since the length of these is 1, the group by returns only one row.

    Hope this helps.

    Best Regards,

    Chris Büttner

  • I knew there was a reason I'd switched to preferring COALESCE over ISNULL... 🙂

    Derek

  • Let's go step by step.

    CREATE TABLE #TEST(A varchar,B varchar(30)); ---> Field named A is of size 1 with type Varchar.

    ISNULL(A,B) ---> means:evaluate the field A, (the first parameter), if has something other than a NULL then return it to me otherwise return me the value converted to Varchar size 1 (As A is Varchar (1)) from varchar size 30 in field B for current record.

    So if you run

    SELECT A, B, ISNULL(A,B),LEN(ISNULL(A,B)) FROM #Test

    A B

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

    A NULL A 1

    NULL Some text S 1

    B NULL B 1

    NULL Some more text S 1

    so if you now run

    SELECT COUNT(*),LEN(ISNULL(A,B))

    FROM #TEST

    GROUP BY LEN(ISNULL(A,B));

    you'll see

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

    4 1

    And

    SELECT A, B, ISNULL(B,A),LEN(ISNULL(B,A)) FROM #Test

    A B

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

    A NULL A 1

    NULL Some text Some text 9

    B NULL B 1

    NULL Some more text Some more text 14

    and run

    SELECT COUNT(*),LEN(ISNULL(B,A))

    FROM #TEST

    GROUP BY LEN(ISNULL(B,A));

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

    2 1

    1 9

    1 14

    Now let's talk about COALESCE(A,B)

    COALESCE returns the first non-null expression among its arguments,

    If you run

    SELECT LEN(COALESCE(A,B)),COALESCE(A,B)

    FROM #TEST

    then

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

    1 A

    9 Some text

    1 B

    14 Some more text

    so

    SELECT COUNT(*),LEN(COALESCE(A,B))

    FROM #TEST

    GROUP BY LEN(COALESCE(A,B));

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

    2 1

    1 9

    1 14

    .

    I hope this has been useful.

    Abdul Rehman

  • Thank you very much for this QOTD! I have been using COALESCE only when I was working with more than two values and was totally unaware of this behavior of ISNULL.

  • It happens every time. I hate being caught with a tricky one.

    Good QD.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Good Question...

  • CREATE TABLE #TEST(A varchar,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');

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

    go

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

    Why above queries are giving differentt results?????????

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

  • CREATE TABLE #TEST(A varchar,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');

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

    go

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

    Why above queries are giving differentt results?????????

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

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

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