CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Michael A
Michael A

Reputation: 9900

Concatenating columns and not including a column when it has a null value?

I have the following table:

ApprovalOrder EntityCode ProjectCode

1 1415 NULL

2 1415 NULL

3 1415 NULL

And the following code:

    select 
        de.ApprovalOrder + de. EntityCode + isnull(de.ProjectCode, '') as 'RowID'
    from workflow.delegation_engine de
    where EntityCode = 1415 and DelegationCode = 'CAT1'

Which is unfortunately returning an additive result. I'm aware this is what it should do given the above however how can I make it concatenate the result of each column into one result whilst also only including de.ProjectCode when the result it not NULL (this can be characters or integer values).

For example, if I was running query just on the first row of this table I'd like the returned result to be '11415'.

Upvotes: 3

Views: 276

Answers (2)

MatBailie
MatBailie

Reputation: 86706

To be explicit about what you are doing, I would cast each item to a VARCHAR() first.

CAST(de.ApprovalOrder AS VARCHAR(16)) + CAST(de.EntityCode AS VARHCAR(16)) + ISNULL(CAST(de.ProjectCode AS VARCHAR(16)), '')


If you want a hack to avoid the explicit casting, start the derivation with a string...

'' + de.ApprovalOrder + de.EntityCode + ISNULL(de.ProjectCode, '')

Upvotes: 4

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You are adding two integer values instead of concatenating two strings. You should first CAST these INTEGER types to a VARCHAR before concatenating them.

SELECT  CAST(de.ApprovalOrder AS VARCHAR(32)) 
        + CAST(de.EntityCode AS VARCHAR(32)) 
        + ISNULL(CAST(de.ProjectCode AS VARCHAR(32)), '') as 'RowID'
FROM    workflow.delegation_engine de
WHERE   EntityCode = 1415 
        AND DelegationCode = 'CAT1'

Note that I assumed a string length of 32 wich is more than enough to hold any INTEGER conversion (10 should be enough)

Upvotes: 4

Related Questions