Conditional split questions

I have a zipcode column that contains xxxxx-xxxx, i want to use conditional split so that i can take the last 4 digits and put them into a different column, I tried to use the SUBSTRING ("ZIP", 6, 4) but it returns an error, any ideas on how i can split it?

Thanks.

Actually, I think you want the derived column transform, not the conditional split. Your SUBSTRING should work fine.|||

I think you want a Derived Column Transfomation too, but you have a couple of mistakes, assuming this is a SSIS expression-

You need to remember the SSIS expression syntax is C style and therefore zero based, so index 7 is the start of the last section, I assume you are skipping the hyphen.

You have used double quotes, this makes it a literal.

Try SUBSTRING(Zip, 7, 4)

Both points don't count if you are still trying to do this on SQL, but I assume you wanted a SSIS solution.

As a rule it helps a lot if you give the details of the error, things like error message, as save a lot of guess work when trying to help you.

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
>