Tricky ...VARCHAR

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Tricky ...VARCHAR

Tricky ...VARCHAR

Rate Topic

Display Mode

Topic Options

Author

Message

Bhuvnesh

Posted Monday, September 15, 2008 10:05 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #569918

GilaMonster

Posted Tuesday, September 16, 2008 2:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

vinod.andani-874416

Posted Monday, March 07, 2011 6:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 07, 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. Smile

Post #1074089

GilaMonster

Posted Monday, March 07, 2011 11:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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 »

Permissions