Concatinate without Nulls

I am building a view and I want to return a combined field of all that make
up a full address (Address1, Address2, City, State and ZipCode). If any of
the fields are Null it returns a null. Do I have to use something like
COALESCE on each one as any of them can be null. Thanks.
DavidUse Isnull(field_name, '') to return an empty string from a null.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:#63iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> I am building a view and I want to return a combined field of all that
make
> up a full address (Address1, Address2, City, State and ZipCode). If any
of
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>|||>> Do I have to use something like COALESCE on each one as any of them can
That is one simple and recommended way to avoid NULLs being returned.
Anith|||You can also use ISNULL.
Example:
select isnull(lastname + ', ', '') + isnull(firstname)
from employees
go
AMB
"David C" wrote:

> I am building a view and I want to return a combined field of all that mak
e
> up a full address (Address1, Address2, City, State and ZipCode). If any o
f
> the fields are Null it returns a null. Do I have to use something like
> COALESCE on each one as any of them can be null. Thanks.
> David
>
>|||You could also investigate the session option
CONCAT_NULL_YIELDS_NULL
Most client tools set this value to ON to give you the behavior you're
seeing. But if want nulls to be treated as empty strings during
concatenation operations, you can
SET CONCAT_NULL_YIELDS_NULL OFF
The setting (as with all SET options) only applies to the current
connection, or, if you set it in a stored procedure, it applies to that
procedure.
Changing this option will also invalidate the use of indexed views or
indexes on computed columns.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:%2363iepDGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>I am building a view and I want to return a combined field of all that make
>up a full address (Address1, Address2, City, State and ZipCode). If any of
>the fields are Null it returns a null. Do I have to use something like
>COALESCE on each one as any of them can be null. Thanks.
> David
>

Concatenation

Hello all,
I'm trying to combine two columns of data into a third column using a formula on the thrid column. Each of the columns could contain nulls and each of the columns could contain padding after or before the data. I'm trying to use the following formula yet SQL is throwing an error. Can someone provide another set of eyes to check this out?
ISNULL(LTRIM(RTRIM([user_Define_4a])),'') + ISNULL(LTRIM(RTRIM([user_Define_1])),'')
Thanksyou need to do the ISNULL before then TRIM. this is because a null value cannot be trimmed and should be converted to an empty string first. try:
LTRIM(RTRIM(ISNULL([user_Define_4a],''))) + LTRIM(RTRIM(ISNULL([user_Define_1],'')))|||Thank you. It works like a champ.

Concatenating in Left Join Query

I need to create a concatenated field based on both sides of a LEFT OUTER
JOIN. When I tried this, I got all nulls in my resultset for that field.
What I wanted was whatever is in the left side concatenated with nothing if
the right side doesn't exist. How can I achieve this. My current SQL is:
Open Order is the problematic field.
<code>
SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT,
B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION,
B.SHP_USR_FLD_03 + B.SHP_USR_FLD_04 AS ORDER_BY,
S.ADDR3 + B.SHP_USR_FLD_05 AS ORDER_EMAIL
FROM SHIPTO S
LEFT OUTER JOIN BLSHPUF B
ON B.COMPANY = S.COMPANY
AND B.CUSTOMER = S.CUSTOMER
AND B.SHIP_TO = S.SHIP_TO
INNER JOIN OECUST O
ON O.COMPANY = S.SHIP_TO
AND O.CUSTOMER = S.CUSTOMER
INNER JOIN ARCUSTOMER A
ON A.COMPANY = S.COMPANY
AND A.CUSTOMER = S.CUSTOMER
</code>
--
-hexaUse IsNull() function Or Coalesce() function
AS In:
SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT,
B.SHP_USR_FLD_01 + IsNull(B.SHP_USR_FLD_02, '') AS ATTENTION,
B.SHP_USR_FLD_03 + IsNull(B.SHP_USR_FLD_04, '') AS ORDER_BY,
S.ADDR3 + IsNull(B.SHP_USR_FLD_05, '') AS ORDER_EMAIL
FROM SHIPTO S
LEFT JOIN BLSHPUF B
ON B.COMPANY = S.COMPANY
AND B.CUSTOMER = S.CUSTOMER
AND B.SHIP_TO = S.SHIP_TO
INNER JOIN OECUST O
ON O.COMPANY = S.SHIP_TO
AND O.CUSTOMER = S.CUSTOMER
INNER JOIN ARCUSTOMER A
ON A.COMPANY = S.COMPANY
AND A.CUSTOMER = S.CUSTOMER
"hexa" wrote:

> I need to create a concatenated field based on both sides of a LEFT OUTER
> JOIN. When I tried this, I got all nulls in my resultset for that field.
> What I wanted was whatever is in the left side concatenated with nothing i
f
> the right side doesn't exist. How can I achieve this. My current SQL is
:
> Open Order is the problematic field.
> <code>
> SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
> A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT
,
> B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION,
> B.SHP_USR_FLD_03 + B.SHP_USR_FLD_04 AS ORDER_BY,
> S.ADDR3 + B.SHP_USR_FLD_05 AS ORDER_EMAIL
> FROM SHIPTO S
> LEFT OUTER JOIN BLSHPUF B
> ON B.COMPANY = S.COMPANY
> AND B.CUSTOMER = S.CUSTOMER
> AND B.SHIP_TO = S.SHIP_TO
> INNER JOIN OECUST O
> ON O.COMPANY = S.SHIP_TO
> AND O.CUSTOMER = S.CUSTOMER
> INNER JOIN ARCUSTOMER A
> ON A.COMPANY = S.COMPANY
> AND A.CUSTOMER = S.CUSTOMER
> </code>
> --
> -hexa|||Thanks, Coalesce is what I needed. My revised query is:
SELECT S.COMPANY, S.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS, A.SEARCH_NAME,
A.CURR_BAL, A.OPEN_ORDS, A.TAX_EXEMPT_CD, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT,
B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION, B.SHP_USR_FLD_03 +
B.SHP_USR_FLD_04 as ORDER_BY,
COALESCE(S.ADDR3 + B.SHP_USR_FLD_05,S.ADDR3,'') AS ORDER_EMAIL,
A1.CUST_USER8 AS TAX_FORM_DT
FROM SHIPTO S
LEFT OUTER JOIN BLSHPUF B
ON B.COMPANY = S.COMPANY
AND B.CUSTOMER = S.CUSTOMER
AND B.SHIP_TO = S.SHIP_TO
INNER JOIN ARCUSTOMER A
ON A.COMPANY = S.COMPANY
AND A.CUSTOMER = S.CUSTOMER
INNER JOIN OECUST O
ON O.COMPANY = S.COMPANY
AND O.CUSTOMER = S.CUSTOMER
INNER JOIN ARCUSTFLDS A1
ON A1.COMPANY = S.COMPANY
AND A1.CUSTOMER = S.CUSTOMER
"CBretana" wrote:
> Use IsNull() function Or Coalesce() function
> AS In:
> SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
> A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT
,
> B.SHP_USR_FLD_01 + IsNull(B.SHP_USR_FLD_02, '') AS ATTENTION,
> B.SHP_USR_FLD_03 + IsNull(B.SHP_USR_FLD_04, '') AS ORDER_BY,
> S.ADDR3 + IsNull(B.SHP_USR_FLD_05, '') AS ORDER_EMAIL
> FROM SHIPTO S
> LEFT JOIN BLSHPUF B
> ON B.COMPANY = S.COMPANY
> AND B.CUSTOMER = S.CUSTOMER
> AND B.SHIP_TO = S.SHIP_TO
> INNER JOIN OECUST O
> ON O.COMPANY = S.SHIP_TO
> AND O.CUSTOMER = S.CUSTOMER
> INNER JOIN ARCUSTOMER A
> ON A.COMPANY = S.COMPANY
> AND A.CUSTOMER = S.CUSTOMER
> "hexa" wrote:
>|||IsNull() is the SAME as Coalesce(). except Coalesce takes an arbitrary
number of arguments, but IsNull() only takes 2 arguments...
So for 2 Arguments, they're the same...
"hexa" wrote:
> Thanks, Coalesce is what I needed. My revised query is:
> SELECT S.COMPANY, S.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS, A.SEARCH_NAME,
> A.CURR_BAL, A.OPEN_ORDS, A.TAX_EXEMPT_CD, O.PO_REQ_FL, O.CIA_FL, O.CIA_P
CT,
> B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION, B.SHP_USR_FLD_03 +
> B.SHP_USR_FLD_04 as ORDER_BY,
> COALESCE(S.ADDR3 + B.SHP_USR_FLD_05,S.ADDR3,'') AS ORDER_EMAIL,
> A1.CUST_USER8 AS TAX_FORM_DT
> FROM SHIPTO S
> LEFT OUTER JOIN BLSHPUF B
> ON B.COMPANY = S.COMPANY
> AND B.CUSTOMER = S.CUSTOMER
> AND B.SHIP_TO = S.SHIP_TO
> INNER JOIN ARCUSTOMER A
> ON A.COMPANY = S.COMPANY
> AND A.CUSTOMER = S.CUSTOMER
> INNER JOIN OECUST O
> ON O.COMPANY = S.COMPANY
> AND O.CUSTOMER = S.CUSTOMER
> INNER JOIN ARCUSTFLDS A1
> ON A1.COMPANY = S.COMPANY
> AND A1.CUSTOMER = S.CUSTOMER
>
> "CBretana" wrote:
>