SQL output is truncated

I have some SQL in MS SQL 2005 that is trimming the result to 10 characters.  Here is the SQL:

ISNULL(CUSTOMERS_1.CUSTNO, LTRIM(RTRIM(dbo.CUSTOMERS.FNAME)) + ' ' + LTRIM(RTRIM(dbo.CUSTOMERS.LNAME)))

This is supposed to look at a given field and if the value is null then essentially concatenate some data together.  The original CUSTOMERS.FNAME = CHRISTOPHER, and the original CUSTOMERS.LNAME = SMITH, but what gets returned is CHRISTOPHE (exactly 10 characters).  I've tried to add CAST or CONVERT, but that didn't seem to help.

Can anyone help me determine why it's shortening the results?
BartWestphalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

computerstreberCommented:
What is the length of CUSTOMERS_1.CUSTNO? When using ISNULL, the length of the first column will be applied to the others, truncating results. Trying using COALESCE instead of ISNULL.
COALESCE(CUSTOMERS_1.CUSTNO, LTRIM(RTRIM(dbo.CUSTOMERS.FNAME)) + ' ' + LTRIM(RTRIM(dbo.CUSTOMERS.LNAME)))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
computerstreberCommented:
Here is an example:
DECLARE	  @test1 CHAR(10)
		, @test2 CHAR(15)
		
SET @test2 = '123456789012345'

SELECT ISNULL(@test1, @test2)
-- returns 1234567890

SELECT COALESCE(@test1, @test2)
-- returns 123456789012345

Open in new window

0
BartWestphalAuthor Commented:
COALESCE appears to work.  That was easy.  In English this means to get the first non null value it finds by evaluating the fields or expressions that are offered (left to right) - correct?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

computerstreberCommented:
That is correct. It returns the first non-null expression among its arguments. ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute as in the following statement:
0
BartWestphalAuthor Commented:
This solved a problem but created another.  A little more background on the issue - this looks to see if there is a ship-to address for a customer, if there is NOT, then it uses the bill-to address.  This is why it checked for presence of a customer number (if there wasn't one then there wasn't a ship-to and it would fill in the ship-to information like name, city, state, zip with bill-to data).  If I use COALESCE it puts the customer number in every field when a ship-to does exist.  I suppose I could use CASE statements.  Thoughts?
0
cyberkiwiCommented:
Hi there,

You can still use ISNULL, just make sure to cast the first param to the right length, e.g.

ISNULL(CAST(CUSTOMERS_1.CUSTNO AS VARCHAR(100)), LTRIM(RTRIM(dbo.CUSTOMERS.FNAME)) + ' ' + LTRIM(RTRIM(dbo.CUSTOMERS.LNAME)))
0
BartWestphalAuthor Commented:
I found something useful about Coalesce but I actually wound up using a CASE statement to check the value of the CUSTNO field and make a change based on that.  All's well that ends well.  Thanks for the info!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.