SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Anders Hellner / Type lengths / Latest Posts

RE: Type lengths

malleswarareddy_m — Thu, 04 Mar 2010 07:57:47 GMT

I got wrong.But the question is good.

RE: Type lengths

JamieX — Fri, 24 Oct 2008 06:48:16 GMT

I almost went 3,3,3 then took a look at the data types again before submitting .. tricky :)

RE: Type lengths

Preet_S — Tue, 16 Sep 2008 07:42:54 GMT

[quote][b]Rajan John (9/9/2008)[/b][hr][quote][b]douglascfast (9/9/2008)[/b][hr]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 gotQuery1-----------211Query2-----------4Query3-----------211Confused - - - as normalDoug[/quote]I also got the same results. Anything missing?[/quote]As Ninja's_RGR'us points out 3,1,3. i.e. Question asks for total number of rows not the Counts returned by those rows.

RE: Type lengths

Preet_S — Tue, 16 Sep 2008 07:38:26 GMT

Questions like these should be worth more than 1 point!Good 'trick' question!

RE: Type lengths

Bhuvnesh — Tue, 16 Sep 2008 03:05:04 GMT

Thanks GILA...i got the point ....

RE: Type lengths

GilaMonster — Tue, 16 Sep 2008 02:55:10 GMT

Because none of the strings you have there are reaching the 30/40 character limit for the varchar. The ISNULL returns for the first query a data type of VARCHAR(30) and for the second query a VARCHAR(40)The 'trick' to the original question was than in one case the ISNULL, because of the order of the parameters passed to it, returned a VARCHAR(1). LEN of a VARCHAR(1) can only be 1 or 0

RE: Type lengths

Bhuvnesh — Mon, 15 Sep 2008 22:00:44 GMT

hi,if you see table creation syntax ,i m using [b]A varchar(40) [/b] instead of [b]A varchar[/b]below queries resluts same but WHY????CREATETABLE #TEST(A varchar(40),B varchar(30)); INSERTINTO #TEST(A) VALUES('A'); INSERTINTO #TEST(B) VALUES('Some text'); INSERTINTO #TEST(A) VALUES('B'); INSERTINTO #TEST(B) VALUES('Some more text'); SELECTCOUNT(*)FROM #TEST GROUP BY LEN(ISNULL(B,A)); goSELECTCOUNT(*) from #TEST GROUP BY LEN(ISNULL(A,B));

RE: Type lengths

Ninja's_RGR'us — Mon, 15 Sep 2008 07:09:28 GMT

Reread the question's explaination.The first parameter in the isnull function determines what data type will be returned. Since one of the column is varchar(1), then when that column is in the parameter 1 spot, isnull returns a varchar(1)... truncating whatever is in column b... but it does not throw a truncation error nor warning for this.That's why it's called a gotcha...

RE: Type lengths

Bhuvnesh — Mon, 15 Sep 2008 05:36:57 GMT

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));goSELECT COUNT(*) from #TEST GROUP BY LEN(ISNULL(A,B));Why above queries are giving differentt results?????????

RE: Type lengths

Bhuvnesh — Mon, 15 Sep 2008 05:29:38 GMT

RE: Type lengths

Anipaul — Thu, 11 Sep 2008 01:16:44 GMT

Good Question...

RE: Type lengths

Tom Garth — Tue, 09 Sep 2008 11:54:18 GMT

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

RE: Type lengths

kevin.l.williams — Tue, 09 Sep 2008 09:26:08 GMT

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.

RE: Type lengths

arehman626 — Tue, 09 Sep 2008 09:06:04 GMT

Let's go step by step.[b]CREATE TABLE #TEST(A varchar,B varchar(30));[i][u][/u][/i][/b] ---> Field named [b]A[/b] is of size 1 with type Varchar.ISNULL(A,B) ---> means:evaluate the field [b]A[/b], (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]B[/b] for current record.So if you run SELECT A, B, ISNULL(A,B),LEN(ISNULL(A,B)) FROM #TestA B ---- ------------------------------ ---- -----------A NULL A 1NULL Some text S 1B NULL B 1NULL Some more text S 1so if you now runSELECT COUNT(*),LEN(ISNULL(A,B))FROM #TESTGROUP BY LEN(ISNULL(A,B));you'll see----------- -----------4 1AndSELECT A, B, ISNULL(B,A),LEN(ISNULL(B,A)) FROM #TestA B ---- ------------------------------ ------------------------------ -----------A NULL A 1NULL Some text Some text 9B NULL B 1NULL Some more text Some more text 14and runSELECT COUNT(*),LEN(ISNULL(B,A))FROM #TESTGROUP BY LEN(ISNULL(B,A));----------- -----------2 11 91 14Now let's talk about [b]COALESCE(A,B)[/b]COALESCE returns the first non-null expression among its arguments,If you run SELECT LEN(COALESCE(A,B)),COALESCE(A,B)FROM #TESTthen----------- ------------------------------1 A9 Some text1 B14 Some more textsoSELECT COUNT(*),LEN(COALESCE(A,B))FROM #TESTGROUP BY LEN(COALESCE(A,B));----------- -----------2 11 91 14.I hope this has been useful.Abdul Rehman

RE: Type lengths

Derek Dongray — Tue, 09 Sep 2008 08:13:13 GMT

I knew there was a reason I'd switched to preferring COALESCE over ISNULL... :)

RE: Type lengths

Christian Buettner-167247 — Tue, 09 Sep 2008 06:50:15 GMT

Hi Doug,thats exactly the purpose of the question.If you execute [code]SELECT ISNULL(A,B) FROM #Test[/code]you will get four 1-char strings because of the implicit conversion.[code]ASBS[/code]Since the length of these is 1, the group by returns only one row.Hope this helps.

RE: Type lengths

Ninja's_RGR'us — Tue, 09 Sep 2008 06:47:30 GMT

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

RE: Type lengths

rajankjohn — Tue, 09 Sep 2008 06:36:16 GMT

[quote][b]douglascfast (9/9/2008)[/b][hr]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 gotQuery1-----------211Query2-----------4Query3-----------211Confused - - - as normalDoug[/quote]I also got the same results. Anything missing?

RE: Type lengths

douglascfast — Tue, 09 Sep 2008 06:21:37 GMT

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 gotQuery1-----------211Query2-----------4Query3-----------211Confused - - - as normalDoug

RE: Type lengths

dunnjoe — Tue, 09 Sep 2008 06:06:15 GMT

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

RE: Type lengths

SuperDBA-207096 — Tue, 09 Sep 2008 05:47:55 GMT

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

RE: Type lengths

Christian Buettner-167247 — Tue, 09 Sep 2008 04:30:58 GMT

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!

Type lengths

anders-731262 — Mon, 08 Sep 2008 21:39:15 GMT

Comments posted to this topic are about the item [B]Type lengths[/B]