asked on

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?

ASKER CERTIFIED SOLUTION

computerstreber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

computerstreber

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

BartWestphal

ASKER

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?

computerstreber

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:

BartWestphal

ASKER

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?

cyberkiwi

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)))

BartWestphal

ASKER

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!