locked
split based on the value in SSIS RRS feed

  • Question

  • Hi i am using SSIS

    I am Derived column

    i need my string split 3 parts

    example: here i am explain explain only 1 row i have 10 million rows now database

    1-8 SUN STREET,YELLOW PARTY ESTATES,SOUTH END,PORT AREA,6116

    1derived upto: 2 common 

    1-8 SUN STREET,YELLOW PARTY ESTATES

    2 derived:2 common to 4th common

    SOUTH END,PORT AREA

    3 derived :remaining data

    6116 (here only single values few rows more than 3 common there (all should 3 derived)

    My destition is address1,address2,address3

    address 1:

    1derived upto: 2 common 

    1-8 SUN STREET,YELLOW PARTY ESTATES

    address 2:

    2 derived:2 common to 4th common

    SOUTH END,PORT AREA

    address  3:

    3 derived :remaining data

    6116 (here only single values few rows more than 3 common there (all should 3 derived)

    Derived 1 purpose i am using this formal:

    SUBSTRING((DT_WSTR,125)[Fulladresss],1,FINDSTRING((DT_WSTR,125)[Fulladresss],"|",2) - 1)

    please help this formal correct or not

    please help remaining formal also

    Thanks

    Aruna

    Friday, August 10, 2018 1:01 PM

Answers

  • Hi ArunaMSBI,

    As Visakh16 mentioned, there are many scenarios to consider here. Before I present my solution, following assumptions were made based on your description:

    1- The input address string can have any number of commas including no commas at all.

    2- If input address string has more than 2 commas but less than 4 commas then characters before 2nd comma will go to Address1 and rest will go to Adress2 till the end of input string including 3rd comma (if exists).

    3- If input address string has more than 4 commas then characters before 2nd comma will go to Address1 field, characters between 2nd comma and 4th comma will go to Adress2 field the rest of the input string (including any following commas) will go to Adress3 field. Also, 2nd and 4th commas will not be included in the output strings.

    4- If there is no comma in the input address, the entire string will go to Adress1 field.

    To test my solution, I have created an Excel sheet with only one column called FullAddress and entered strings with all possible scenarios that I mentioned above including the address you mentioned in your post. I then used a derived column transformation and used following expressions for the three derived columns:

    //Address1
    FINDSTRING(FullAddress,",",2) != 0 ? SUBSTRING(FullAddress,1,FINDSTRING(FullAddress,",",2) - 1) : FullAddress
    
    //Address2
    FINDSTRING(FullAddress,",",4) != 0 ? SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",2) + 1,FINDSTRING(FullAddress,",",4) - FINDSTRING(FullAddress,",",2) - 1) : FINDSTRING(FullAddress,",",2) != 0 ? SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",2) + 1,LEN(FullAddress) - FINDSTRING(FullAddress,",",4)) : ""
    
    //Address3
    FINDSTRING(FullAddress,",",4) != 0 ? SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",4) + 1,LEN(FullAddress) - FINDSTRING(FullAddress,",",4)) : ""

    This gives me the expected output for all mentioned scenarios. See below image for the result displayed via "Data Viewer" after the Derived column transformation:

    Note: You may have to add data type casting in the expression (if applicable).

    Hope this solves your problem. Thanks


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    • Proposed as answer by Pirlo Zhang Monday, August 13, 2018 1:48 AM
    • Marked as answer by ArunaMSBI Monday, August 13, 2018 5:48 AM
    Friday, August 10, 2018 6:22 PM

All replies

  • sorry you rules are not clear

    what if actual string had only 3 parts separated by ,

    in that case how do you want to split it

    what if there are more parts

    So for questions like this you need to give us samples of all possible scenarios and then how you want result to come in each case


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, August 10, 2018 1:58 PM
  • Hi ArunaMSBI,

    As Visakh16 mentioned, there are many scenarios to consider here. Before I present my solution, following assumptions were made based on your description:

    1- The input address string can have any number of commas including no commas at all.

    2- If input address string has more than 2 commas but less than 4 commas then characters before 2nd comma will go to Address1 and rest will go to Adress2 till the end of input string including 3rd comma (if exists).

    3- If input address string has more than 4 commas then characters before 2nd comma will go to Address1 field, characters between 2nd comma and 4th comma will go to Adress2 field the rest of the input string (including any following commas) will go to Adress3 field. Also, 2nd and 4th commas will not be included in the output strings.

    4- If there is no comma in the input address, the entire string will go to Adress1 field.

    To test my solution, I have created an Excel sheet with only one column called FullAddress and entered strings with all possible scenarios that I mentioned above including the address you mentioned in your post. I then used a derived column transformation and used following expressions for the three derived columns:

    //Address1
    FINDSTRING(FullAddress,",",2) != 0 ? SUBSTRING(FullAddress,1,FINDSTRING(FullAddress,",",2) - 1) : FullAddress
    
    //Address2
    FINDSTRING(FullAddress,",",4) != 0 ? SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",2) + 1,FINDSTRING(FullAddress,",",4) - FINDSTRING(FullAddress,",",2) - 1) : FINDSTRING(FullAddress,",",2) != 0 ? SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",2) + 1,LEN(FullAddress) - FINDSTRING(FullAddress,",",4)) : ""
    
    //Address3
    FINDSTRING(FullAddress,",",4) != 0 ? SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",4) + 1,LEN(FullAddress) - FINDSTRING(FullAddress,",",4)) : ""

    This gives me the expected output for all mentioned scenarios. See below image for the result displayed via "Data Viewer" after the Derived column transformation:

    Note: You may have to add data type casting in the expression (if applicable).

    Hope this solves your problem. Thanks


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    • Proposed as answer by Pirlo Zhang Monday, August 13, 2018 1:48 AM
    • Marked as answer by ArunaMSBI Monday, August 13, 2018 5:48 AM
    Friday, August 10, 2018 6:22 PM