FINDSTRING Function
-
Hi All,
We are using FINDSTRING Function in one of Data flow Derived Column (Component), It work Fine for Symbols like '&','%' and ' (Single Quote)
Where as we need to implement it for " (Double Quote) and \ (Slash).
FINDSTRING ("STRING&32","&") != 0 Works
FINDSTRING ("STRING&32","\") != 0 Not Working Working (Its shows Syntax Error)
FINDSTRING ("STRING&32","\\") != 0 Not Working Working (Syntax works, but Actual Functionality Does not)
FINDSTRING ("STRING&32","\"") != 0 Not Working Working (Syntax works, but Actual Functionality Does not)
Please need help.
-
please needed help....!
-
sudhirnune (8/24/2012)
Hi All,We are using FINDSTRING Function in one of Data flow Derived Column (Component), It work Fine for Symbols like '&','%' and ' (Single Quote)
Where as we need to implement it for " (Double Quote) and \ (Slash).
FINDSTRING ("STRING&32","&") != 0 Works
FINDSTRING ("STRING&32","\") != 0 Not Working Working (Its shows Syntax Error)
FINDSTRING ("STRING&32","\\") != 0 Not Working Working (Syntax works, but Actual Functionality Does not)
FINDSTRING ("STRING&32","\"") != 0 Not Working Working (Syntax works, but Actual Functionality Does not)
Please need help.
Hi sudhirnune
Based on the sample code provide, it seems your target string STRING&32 does not contains the text you are trying to find.
FINDSTRING ("STRING&32","&") returns 7 because the '&' is at position 7
FINDSTRING ("STRING&32","\") fails with an error becase the \ is escaping the closing ".
FINDSTRING ("STRING&32","\\") returns zero because there is not back slash in the target text STRING&32; you should try with a testing string containing a back slash.
FINDSTRING ("STRING&32","\"") returns zero because the target text STRING&32 does not contain a double quote, you should try with a test string containing double quotes.
Cheers,
Hope this helps,
Rock from VbCity -
Cheers,
Hope this helps,
Rock from VbCity -
Sorry for the Wrong Eample above, below is what is needed
FINDSTRING ("STRING&32","&") != 0 Works
FINDSTRING ("STRING\32","\") != 0 Not Working Working (Its shows Syntax Error)
FINDSTRING ("STRING\32","\\") != 0 Not Working Working (Syntax works, but Actual Functionality Does not)
FINDSTRING ("STRING\32","\"") != 0 Not Working Working (Syntax works, but Actual Functionality Does not)
-
Hi sudhirnune
If you are hard coding "STRING\32" in your FINDSTRING tests, then you should mask the back slash as well, the string "STRING\32" is interpreted as "STRING32" because the back slash before the number 3 acts as a masking, you may try "STRING\\32", here a back-slash masked a back slash resulting on the string 'STRING\32' you are trying to use in your tests.
NOTE:
When you enter text in code enclosing it in double quotes, like "text" and you want it to contain 'control' characters like the back-slash then you have to 'escape' the back-slash with itself, like '\\'; if your function is applied to a variable (coming from an external source) then you do not need the 'escaping'.
Hope this helps,
Cheers,
Hope this helps,
Rock from VbCity -
Thanks for the Details.
I hope I made my Examples more Complex, than needed to get help.
The Requiremnet is Simple, when the Input Data Columns have the Following Charecters ( ' (Single Code) , " (Double Code) , & , ~ , \)
we need to route the complete Row to Error Data.
For finding the special charecters we are using FINDSTRING Function, which is working Perfectly fine for ( &, ' (single code) , ~) and not for (" (Double Code) and \ ).
Please need help to understand what make the findsting to find these two charecter too (" (Double Code) and \ ).
-
Hi sudhirnune
It should work for you as explained, I did a test to prove it, let me explain what I did.
1. Found an Address table with 16,358 rows.
2. Modified 3 of these records, adding double quoted and back-slashed characters into its [AddressLine1] column (see figure 1)
As we do not have detailed details of your package and components, I created my own package with a Data Flow illustrated by figure 2.
It has a OLE DB data source reading the Address table, a Derived Column component applying the FINDSTRING function to identify those columns having back-slash or double quotes in its [AddreessLine1] column, nothing fancy, next the Conditional Split component was used to split the data based on the columns added by the previous component.
The Derived Column component just adds two columns with yes or no values based on finding back-slashes or double quotes on the [AddressLine1] column.
The package ends with records count components, telling us that 3 rows were identified as having those characters.
My test proves that these are the way to implement the FINDSTRING function.
FINDSTRING(AddressLine1,"\\",1)
FINDSTRING(AddressLine1,"\"",1)
Note: I used MS Sql Server 2008r2, and BIDS 2008.
Hope this helps,
Rock from VbCity -
Hi Rock,
Thanks for the Example and Data, It works for me it the Source is SQL DB, My Source here is Excel File.
From which I retrive the data.
I will Test once again and check.
-
Great! Thanks for this example. Rumor is 2012 has a fix for the flat file import but till then this ia by far the best example I have seen!
Chris
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply