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

Tricky ...VARCHAR Expand / Collapse
Author
Message
Posted Monday, September 15, 2008 10:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
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
Post #569918
Posted Tuesday, September 16, 2008 2:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #570010
Posted Monday, March 7, 2011 6:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 7, 2012 5:25 AM
Points: 231, Visits: 61
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.
Post #1074089
Posted Monday, March 7, 2011 11:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
Please note: 3 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1074358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse