current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 1 down vote favorite

My field in my SKU table

(BI.dbo.SKU.phl5) is varchar(15)

However below code returns just 3 characters 'Unc' for the null fields in my table while it should return 'Uncategorized'. How to solve that?

ISNULL(SUBSTRING(BI.dbo.SKU.phl5,0,3),'Uncategorized') AS phl1
share|improve this question
    
Of course it returns just 3 characters, ISNULL takes two parameters, and returns the first, unless it is NULL, in which case it returns the second. So obviously, if phl5 contains "Uncategorized", you'll return "Unc" since ISNULL will return the result of the substring. Please explain the logic behind what you want (not the code logic, the "thinking" logic, what you want and why) – Lasse V. Karlsen Jun 5 '13 at 8:29
    
@LasseV.Karlsen But phl5 does not contain "Uncategorized", What I am tring to do is that, when phl5 is not null, return the first 3 character, if it is null, return "Uncategorized", I tried this with the above code. However when it is null, the above code returns 'Unc' – HOY Jun 5 '13 at 8:37
up vote 4 down vote accepted 
ISNULL(CAST(SUBSTRING(BI.dbo.SKU.phl5,0,3) AS VARCHAR(13)),'Uncategorized') AS phl1

The size of the return type of SUBSTRING isn't clearly documented that I can find, but the problem is that the type of ISNULL is the type of the first expression, which is clearly coming back as VARCHAR(3) since you are truncating it to 3 characters.

ISNULL docs

share|improve this answer
up vote 0 down vote

Try this

CASE WHEN BI.dbo.SKU.phl5 IS NULL THEN 'Uncategorized' 
     ELSE SUBSTRING(BI.dbo.SKU.phl5,0,3) 
END AS phl1
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.