Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Type lengths Expand / Collapse
Author
Message
Posted Monday, September 8, 2008 9:39 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:13 AM
Points: 984, Visits: 350
Comments posted to this topic are about the item Type lengths
Post #565885
Posted Tuesday, September 9, 2008 4:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
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
Posted Tuesday, September 9, 2008 5:47 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 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
Posted Tuesday, September 9, 2008 6:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Tuesday, September 9, 2008 6:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 2:58 AM
Points: 46, Visits: 205
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
Posted Tuesday, September 9, 2008 6:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
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?
Post #566100
Posted Tuesday, September 9, 2008 6:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 20,572, Visits: 9,617
Yes, that's 3 rows, 1 row, 3 rows...
Post #566113
Posted Tuesday, September 9, 2008 6:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
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
Posted Tuesday, September 9, 2008 8:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,274, Visits: 1,983
I knew there was a reason I'd switched to preferring COALESCE over ISNULL... :)

Derek
Post #566183
Posted Tuesday, September 9, 2008 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 1:42 PM
Points: 5, Visits: 40
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse