Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to avoid technically the null values while concatenating columns


How to avoid technically the null values while concatenating columns

Author
Message
manibad
manibad
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 143
HI,
In SSIS by using dervied column i manipulate fields and end up in my desired results.
But for an record if the particular column what i am using has NULL value then the particular record where i have given space got truncated and my file layout becomes a mess.
So to avoid that i check whether a column is not null or not if its null means i would feed spaces.So my layout doesn't change.
But this will be hectic if i am using multiple columns for a single ouptut column.

Do we have some other way other to avoid NULL values?
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8404 Visits: 19509
Your message is a little confusing: are you importing or (I think) exporting?

What do you mean when you say that your file layout "becomes a mess"? Can you provide some sample data highlighting the problem?

Instead of 'passing spaces', what's wrong with an empty string?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dg227
dg227
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 808
If, as Phil has assumed/you've implied, you are exporting data from multiple source columns and concatenating them into a single export/destination column, you still need to account for the NULL fields in that concatenated string. Whether your final "export column" is in a fixed-width, or otherwise delimited format, you'd still want to pad those NULLed fields with some acceptable value (spaces, in a fixed-width file, for example). If you don't account for those NULL values/fields, then your export format/layout will definitely be "off."

If this is not what you're after, please detail your situation further.
sgmunson
sgmunson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2867 Visits: 3775
There may be an easier way. If the data source allows an SQL Query, and supports the ISNULL function, then your SELECT statement could look something like this:


SELECT Field1, Field2,
ISNULL(Field3, '') + ISNULL(Field4, '') AS CombinedFields3and4
FROM dbo.SomeTable
[code]

One other possiblity is if it also supports the NULLIF function:

[code]
SELECT Field1, Field2,
NULLIF(ISNULL(Field3, '') + ISNULL(Field4, ''), '') AS CombinedFields3and4
FROM dbo.SomeTable
[code]

... which would only null the field if both sourc fields were null.

Alternatively, there is a "Conditional" operator for SSIS that you can use in a Derived Column transformation, with a syntax something like this:

[code]
ISNULL(Field3) ? ValueExpressionWhenTrue : ValueExpressionWhenFalse



... that can be used to detect a NULL value.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
manibad
manibad
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 143
dg227 (4/24/2013)
If, as Phil has assumed/you've implied, you are exporting data from multiple source columns and concatenating them into a single export/destination column, you still need to account for the NULL fields in that concatenated string. Whether your final "export column" is in a fixed-width, or otherwise delimited format, you'd still want to pad those NULLed fields with some acceptable value (spaces, in a fixed-width file, for example). If you don't account for those NULL values/fields, then your export format/layout will definitely be "off."

If this is not what you're after, please detail your situation further.

I am actually after this only.how it can be done ???
manibad
manibad
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 143
sgmunson (4/25/2013)
There may be an easier way. If the data source allows an SQL Query, and supports the ISNULL function, then your SELECT statement could look something like this:


SELECT Field1, Field2,
ISNULL(Field3, '') + ISNULL(Field4, '') AS CombinedFields3and4
FROM dbo.SomeTable
[code]

One other possiblity is if it also supports the NULLIF function:

[code]
SELECT Field1, Field2,
NULLIF(ISNULL(Field3, '') + ISNULL(Field4, ''), '') AS CombinedFields3and4
FROM dbo.SomeTable
[code]

... which would only null the field if both sourc fields were null.

Alternatively, there is a "Conditional" operator for SSIS that you can use in a Derived Column transformation, with a syntax something like this:

[code]
ISNULL(Field3) ? ValueExpressionWhenTrue : ValueExpressionWhenFalse



... that can be used to detect a NULL value.


ya ya your are right..By using Conditional operator only i am able to achieve my goal.but it is easily possible if i am using 2 columns but if i am used to manipulate with 4-6 columns and each column are independent ones then its become difficult to achieve it.
sgmunson
sgmunson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2867 Visits: 3775
The number of fields that can be included in the query's use of ISNULL and NULLIF is not limited to two. You can just keep concatenating an ISNULL function, and then NULLIF the entire concatenation, and thus have as many fields as you need. They just all need to be strings, or alternately, CAST-ed or CONVERT-ed to strings.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8404 Visits: 19509
sgmunson (5/6/2013)
The number of fields that can be included in the query's use of ISNULL and NULLIF is not limited to two. You can just keep concatenating an ISNULL function, and then NULLIF the entire concatenation, and thus have as many fields as you need. They just all need to be strings, or alternately, CAST-ed or CONVERT-ed to strings.


Steve - you sound like you understand the problem - can you explain it, because I still cannot comprehend properly what the issue is? :-)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sgmunson
sgmunson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2867 Visits: 3775
I'm at the point of making an assumption about what the poster meant by their most recent post. Given the technique of concatenating a series of ISNULL functions whose parameters are all strings or casted or converted into strings, it's not that difficult to eliminate a null value from causing a problem in concatenation, and you just encapsulate the entire concatenation of ISNULLs within a NULLIF and you can preserve a NULL value for the case when all the fields are NULL as opposed to just one or more but not all. The assumption I make is that when they mention the idea of having 4 to 6 fields to deal with, that they mean that they need to concatenate more than just two fields. Whether that assumption holds true or not is something the poster would have to resolve for us by posting again. Does that explain?

Phil Parkin (5/6/2013)
sgmunson (5/6/2013)
The number of fields that can be included in the query's use of ISNULL and NULLIF is not limited to two. You can just keep concatenating an ISNULL function, and then NULLIF the entire concatenation, and thus have as many fields as you need. They just all need to be strings, or alternately, CAST-ed or CONVERT-ed to strings.


Steve - you sound like you understand the problem - can you explain it, because I still cannot comprehend properly what the issue is? :-)


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8404 Visits: 19509
OK, I see what you are getting at, thanks. I still cannot see why that causes a formatting problem when outputting though.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search