April 1, 2014 at 9:10 am
Hi All,
I have the following Expression working on a Derived Column in an SSIS Data Flow Transformation
[Date_Start] == " 0 0- 0- 0" ? NULL(DT_WSTR,10) : SUBSTRING([Date_Start],5,4) + "-" + SUBSTRING([Date_Start],3,2) + "-" + SUBSTRING([Date_Start],1,2)
However
0 0- 0- 0
values are not being replaced with NULLs by this expression.
I have been trying to resolve this for the best part of a day so at this point any suggestions/advice would be appreciated...Thanks
April 1, 2014 at 9:15 am
In your expression, there is a space at the start of the string. Is that how your data looks?
John
April 1, 2014 at 9:19 am
Hi John,
I have tried with and without spaces at the start but made no difference
April 1, 2014 at 9:23 am
The current string in the expression is
"0 0- 0- 0"
April 2, 2014 at 3:42 am
Hi All,
I have the following Expression working on a Derived Column in an SSIS Data Flow Transformation
[Date_Start] == " 0 0- 0- 0" ? NULL(DT_WSTR,10) : SUBSTRING([Date_Start],5,4) + "-" + SUBSTRING([Date_Start],3,2) + "-" + SUBSTRING([Date_Start],1,2)
However
0 0- 0- 0
values are not being replaced with NULLs by this expression.
I have been trying to resolve this for the best part of a day so at this point any suggestions/advice would be appreciated...Thanks
OK,
I have investigated this further . THe issue is not with the string
" 0 0- 0- 0" as I said previously.
The string I am trying to filter out is "0 0 0 0"
what is outputted is not NULL for this value but "0 0- 0- 0"
The attachment may help with this...
I have tried applying a number of expresions to this column
examples below
[MSTUB-xInt_Only-Rev-Dt] == "00000000" || [MSTUB-xInt_Only-Rev-Dt] == "0 0 0 0" || [MSTUB-xInt_Only-Rev-Dt] == "0 0- 0- 0" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)
[MSTUB-xInt_Only-Rev-Dt] == "0" + " " + "0" + " " + "0" + " " + "0" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)
There seems to be an issue recognizing the spaces between the 0s ?
April 2, 2014 at 4:03 am
Not all spaces are created equal. Maybe they're tabs or something else that could look like a space. There's a function (ASCII or something like that) that will return the ASCII value for a given character. It may be worth using that to check that the character you're checking for is the same as the ones in your string.
John
April 2, 2014 at 8:48 am
Hi,
using TRIM solves the issue
TRIM([MSTUB-xInt_Only-Rev-Dt]) == "0 0 0 0" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy