none
FINDSTRING Function in SSIS

    Question

  • Hi Everybody,

    I need to write the below expressions in Derived Column Transformation. But It is throwing a warning. Data type issues. I just know theorytically that FINDSTRING function needs DT_WSTR data type. But I never use it. Please help me.

    FINDSTRING( [Customer Number] , "CR", 3 )  ? "21" :  "31"

    However my ultimate requirement is if this customer number column contains any values having CR then it should be replaced as "21".

    For example:

    Source - Customer Number                                                   Target - Final Column

    CR1034                                                                                21
    1035CR                                                                                21
    1037                                                                                    31
    10CR39                                                                                21

    To achieve this I hope FINDSTRING function can be helpful. Please correct me if I am wrong.


    Thanks in advance.

                      

    Saturday, September 05, 2009 1:22 PM

Answers

  • FINDSTRING([Customer Number],"CR",1) > 0 ? "21" : "31"

    [Customer Number] is string


    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 05, 2009 2:02 PM

All replies

  • I need to just write the above expressions with proper data type. Please help.


    Many tahnks.
    Saturday, September 05, 2009 1:43 PM
  • FINDSTRING((DT_WSTR,50)[Customer Number],"CR",3) ? "21" : "31"


    Saturday, September 05, 2009 1:44 PM
  • FINDSTRING([Customer Number],"CR",1) > 0 ? "21" : "31"

    [Customer Number] is string


    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 05, 2009 2:02 PM
  • Many thanks your reply... But i just tried with below expression and its working fortunately.

    (DT_BOOL)FINDSTRING([Invoice Number],"CR",1) ? "21" : "31"

    Please correct me if I am wrong. Or any demerits with using DT_BOOL?
    Saturday, September 05, 2009 2:17 PM
  • I think no demerits except casting the expression to DT_BOOL when you can achieve the same thing without using DT_BOOL  :)

    Please close the thread
    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 05, 2009 2:21 PM
  • Excellent Nitesh,

    Just now I used your logic and its working.. :)  Thanks.


    Phil,

    Many thanks for your overwhelming solution as well.


    Take care guys,

    Nihar Parida
    Saturday, September 05, 2009 2:41 PM