Post reply

Findstring question

  • March 12, 2008 at 2:30 pm

    #128297

    Hi guys. I am nwe to SSIS and studying SSIS, and while i was going over some books, I saw following express:

    SUBSTRING(ProductName,1,(FINDSTRING(ProductName,"Size",1) - 2))

    My understanding for this code is from product name field, from position 1, find a string starting with Size and remove them. But there is -2 after findstring. what is this -2 do?

    Thank you.

  • March 12, 2008 at 2:34 pm

    #788797

    It simply substracts 2 from the character position returned by the FINDSTRING.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 12, 2008 at 7:15 pm

    #788845

    i think this would fail if it finds "Size" in the beginning of string. How will it substract 2 chars if current position of the string is 1?

    I haven't tested that but seems logical from the statement.

  • March 13, 2008 at 7:44 pm

    #789395

    You are correct; a match found at the beginning of the string would result in a negative length for the outer SUBSTRING function, resulting in an error.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • July 18, 2008 at 12:43 pm

    #845213

    Hi All,

    why I am not able to use FINDSTRING function in Script component

    I am writng a below code

    var1 = findstring(Row.PubDt, "-", 1)

    This throws an error saying findstirng is not declared.

    if I can;t use this function in script component what function I can use to find whether the string contains a character I am looking for.

    Thanks in Advance

    Regards

    -Srinath

  • July 18, 2008 at 1:08 pm

    #845222

    InStr([start,] string1, string2 [, compare])

    Hope this helps,

    Chad

  • March 24, 2009 at 10:14 am

    #965370

    instr does not work in SSIS..What else can i use..I have similar issue where my findstring returns 0 and I have a -1 in my substring so the substring is failing for the rows that do not have the value I am looking for...How do I make this work with a case statement ?

  • March 24, 2009 at 2:29 pm

    #965608

    Instr is the PL/SQL equivalent of Substring. In SSIS try the Substring function.

    Hope that helps.

  • March 24, 2009 at 7:03 pm

    #965751

    ssexton (3/24/2009)

    Instr is the PL/SQL equivalent of Substring. In SSIS try the Substring function.

    Hope that helps.

    Ummm..... no it's not... INSTR in PL/SQL is the equivalent of CHARINDEX in T-SQL. SUBSTR in PL/SQL is the equivalent of SUBSTRING in T-SQL except that SUBSTR in PL/SQL can count from the right as well as the left.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 25, 2009 at 7:47 am

    #965984

    My apologies, you are correct. My response was too brief to be informative (as informative as yours). It was purely meant to provide an alternative for the Instr function in the previous example provided.

    Since neither instr or charindex is an option with respect to helping him find the solution within SSIS, SUBSTRING was the alternative suggestion.

    Hope that clarifies.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply