Type lengths

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Anders Hellner » Type lengths

23 posts, Page 1 of 3

1 2 3 »»»

Type lengths

Rate Topic

Display Mode

Topic Options

Author

Message

anders-731262

Posted Monday, September 08, 2008 9:39 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 28, 2013 7:38 AM
Points: 894, Visits: 317

Comments posted to this topic are about the item Type lengths

Post #565885

Christian Buettner-167247

Posted Tuesday, September 09, 2008 4:30 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 2,526, Visits: 3,620

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

Post #566018

SuperDBA-207096

Posted Tuesday, September 09, 2008 5:47 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711

Nice one!

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

Post #566058

dunnjoe

Posted Tuesday, September 09, 2008 6:06 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, March 31, 2011 1:40 AM
Points: 1,938, Visits: 208

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

Post #566071

douglascfast

Posted Tuesday, September 09, 2008 6:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 4:29 AM
Points: 40, Visits: 173

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

Post #566087

rajankjohn

Posted Tuesday, September 09, 2008 6:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:53 PM
Points: 1,199, Visits: 568

douglascfast (9/9/2008)

I also got the same results. Anything missing?

Post #566100

Ninja's_RGR'us

Posted Tuesday, September 09, 2008 6:47 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 21,357, Visits: 9,538

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

Post #566113

Christian Buettner-167247

Posted Tuesday, September 09, 2008 6:50 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 2,526, Visits: 3,620

Hi Doug,

thats exactly the purpose of the question.
If you execute

SELECT ISNULL(A,B) FROM #Test

you 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

Post #566120

Derek Dongray

Posted Tuesday, September 09, 2008 8:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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

Derek

Post #566183

arehman626

Posted Tuesday, September 09, 2008 9:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 07, 2012 11:48 AM
Points: 5, Visits: 38

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

Post #566241

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions