locked
Is it possible to use the % character? RRS feed

  • Question

  • Is it possible to write an expression to filter the data but the expression looks like:

    'Pass all rows in the pipeline that column A has teh value like bcd.
    For example:
    ...where column A like '%bcd%'
    So
    'abcd'
    And
    'rererebcdjhjh'
    Will pass while
    'bcfffff'
    Will not pass...

    I know I can use teh conditional task but how to write teh expression?
    Thanks!

    lekfir
    Friday, January 16, 2009 8:55 PM

Answers

  • If it's a simple "LIKE" type expression, then yes, the Conditional Split can work.  Use the FINDSTRING function in the expression.

    If it's more complicated, you might want to try a Regular Expression component.  Microsoft has made one (as a separate download) and SQLIS.com has two (for slightly different purposes).  See the Community Tasks and Components site for links to those.
    Todd McDermid's Blog
    • Marked as answer by lekfir Saturday, January 17, 2009 8:20 AM
    Friday, January 16, 2009 9:40 PM
  • Oh - yes.  The FINDSTRING function returns the index in the string where the expression was found.  So in order to use it in a Conditional Split, you need to compare it to zero in order to get a boolean.  " == 0" means your substring can't be found, "> 0" means the substring was found.  Like so:

    FINDSTRING([Column A], "test", 1) > 0 

    Todd McDermid's Blog
    Friday, January 16, 2009 11:00 PM

All replies

  • lekfir said:

    Is it possible to write an expression to filter the data but the expression looks like:

    'Pass all rows in the pipeline that column A has teh value like bcd.
    For example:
    ...where column A like '%bcd%'
    So
    'abcd'
    And
    'rererebcdjhjh'
    Will pass while
    'bcfffff'
    Will not pass...

    I know I can use teh conditional task but how to write teh expression?
    Thanks!


    lekfir



    You mean you are trying to write an expression that will evaluate to the above?
    Why can't you do it?

    So, if you want your SQL statement to say

    Select A from Table Where A like '%bcd%'

    then your expression is that whole thing wrapped around double quotes, like

    "Select A from Table Where A like '%bcd%'"

    This is too simple, so perhaps I missed something in your question?
    Friday, January 16, 2009 9:10 PM
  • Thanks for your response.
    Let me be more clear:
    I am using a 'Conditional Split' task.
    In the Condition field I want to write a condition that returns TRUE only if the value in Column A contains the value like %bcd%
    lekfir
    Friday, January 16, 2009 9:19 PM
  • If it's a simple "LIKE" type expression, then yes, the Conditional Split can work.  Use the FINDSTRING function in the expression.

    If it's more complicated, you might want to try a Regular Expression component.  Microsoft has made one (as a separate download) and SQLIS.com has two (for slightly different purposes).  See the Community Tasks and Components site for links to those.
    Todd McDermid's Blog
    • Marked as answer by lekfir Saturday, January 17, 2009 8:20 AM
    Friday, January 16, 2009 9:40 PM
  • Thanks Todd,
    So if I want to check if Column A contains the string 'test' it will look like:
    FINDSTRING([Column A], "test", 1)

    ?
    Thanks!

    lekfir
    Friday, January 16, 2009 10:37 PM
  • Yes!
    Todd McDermid's Blog
    Friday, January 16, 2009 10:43 PM
  • Well, it keeps getting 'red' and fails to accept the expression.
    I even changed the column type to WSTR but it fails...;-(
    Any idea?


    lekfir
    Friday, January 16, 2009 10:52 PM
  • Oh - yes.  The FINDSTRING function returns the index in the string where the expression was found.  So in order to use it in a Conditional Split, you need to compare it to zero in order to get a boolean.  " == 0" means your substring can't be found, "> 0" means the substring was found.  Like so:

    FINDSTRING([Column A], "test", 1) > 0 

    Todd McDermid's Blog
    Friday, January 16, 2009 11:00 PM
  • Thanks!
    That worked fantastic!!!
    lekfir
    Saturday, January 17, 2009 8:20 AM