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


FINDSTRING Function


FINDSTRING Function

Author
Message
sudhirnune
sudhirnune
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 252
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.
sudhirnune
sudhirnune
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 252
please needed help....!
Rock from VbCity
Rock from VbCity
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1038
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
Rock from VbCity
Rock from VbCity
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1038
Cheers,

Hope this helps,
Rock from VbCity
sudhirnune
sudhirnune
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 252
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)
Rock from VbCity
Rock from VbCity
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1038
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
sudhirnune
sudhirnune
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 252
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 \ ).
Rock from VbCity
Rock from VbCity
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1038
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
Attachments
Table_01.png (194 views, 11.00 KB)
Pkg_01.png (195 views, 28.00 KB)
sudhirnune
sudhirnune
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 252
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.
Chris Ammann
Chris Ammann
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 124
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
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