Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FINDSTRING Function Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 9:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:47 PM
Points: 86, Visits: 214
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.

Post #1349996
Posted Sunday, August 26, 2012 11:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:47 PM
Points: 86, Visits: 214
please needed help....!
Post #1350202
Posted Monday, August 27, 2012 12:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 23, 2013 2:42 AM
Points: 126, Visits: 1,016
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
Post #1350212
Posted Monday, August 27, 2012 12:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 23, 2013 2:42 AM
Points: 126, Visits: 1,016
Cheers,

Hope this helps,
Rock from VbCity
Post #1350213
Posted Tuesday, August 28, 2012 2:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:47 PM
Points: 86, Visits: 214
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)

Post #1350731
Posted Tuesday, August 28, 2012 8:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 23, 2013 2:42 AM
Points: 126, Visits: 1,016
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
Post #1351372
Posted Wednesday, August 29, 2012 9:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:47 PM
Points: 86, Visits: 214
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 \ ).
Post #1351988
Posted Thursday, August 30, 2012 1:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, November 23, 2013 2:42 AM
Points: 126, Visits: 1,016
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


  Post Attachments 
Table_01.png (95 views, 11.05 KB)
Pkg_01.png (95 views, 29.00 KB)
Post #1352033
Posted Thursday, August 30, 2012 4:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:47 PM
Points: 86, Visits: 214
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.
Post #1352105
Posted Saturday, December 01, 2012 9:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 176, Visits: 110
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
Post #1391648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse