locked
Removing Commas in dervied column? RRS feed

  • Question

  • Hi Friends,


    i have a column having some records like ",Clayton" and  "Township," and "Clayton, Dept"   ...

    i want to remove unnecessary commas which is presented in the 1st two records...

    how to get a derived column condition for this?

    Thanks in advance
    Monday, January 23, 2012 12:39 PM

Answers

  • If I'm understanding your requirement correctly, you only want to remove commas if they exist as the first or last character (#1 and #2), but not the comma that actually separates 2 values.  If that's the case, you can use a couple of approaches.  I've broken the expression up into 2 separate derived column expressions so that it's easier to understand, maintain and debug.  Could easily combine the 2 if you'd rather have a single derived column component in your data flow but with a gnarlier expression. 

    Here are the expressions.  I just named the original column "input" and the result of the 1st derived column that feeds the 2nd derived column "adjustedInput":

    --remove last comma
    RIGHT(input,1) == "," ? REPLACE(input,",","") : input
    
    --remove first comma
    FINDSTRING(input,",",1) == 1 ? SUBSTRING(adjustedInput,2,LEN(adjustedInput)) : adjustedInput

    Hope that helps.

     

     


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com

     


    it worked but with slight modifications in the condition....

    Any way, Thank you so much buddy
    • Marked as answer by Prakash.R Monday, January 23, 2012 3:00 PM
    • Edited by Prakash.R Monday, January 23, 2012 3:00 PM
    Monday, January 23, 2012 3:00 PM
  • Prakash -

    Glad you were able to get to a solution.  If my post was part of that solution, please mark it so.  Or mark it as helpful.

    Also, would be appreciated if you would post your final solution to share with the community.  Someone else may have a similar challenge at some point, and samples are always helpful.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com
    • Edited by Brent Greenwood Tuesday, January 24, 2012 2:31 AM
    • Marked as answer by Prakash.R Tuesday, January 24, 2012 8:40 AM
    Tuesday, January 24, 2012 2:31 AM

All replies

  • I am going to assume that there is just one comma at the beginning and one at the end.  Try the following expression (replace the variable with your column name):

    REPLACE(  REPLACE(REPLACE( "%%" + TRIM( @[User::Test1]) + "%%","%%,","%%"), ",%%", "%%")  , "%%", "" )
    

     


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    Monday, January 23, 2012 1:21 PM
  • If I'm understanding your requirement correctly, you only want to remove commas if they exist as the first or last character (#1 and #2), but not the comma that actually separates 2 values.  If that's the case, you can use a couple of approaches.  I've broken the expression up into 2 separate derived column expressions so that it's easier to understand, maintain and debug.  Could easily combine the 2 if you'd rather have a single derived column component in your data flow but with a gnarlier expression. 

    Here are the expressions.  I just named the original column "input" and the result of the 1st derived column that feeds the 2nd derived column "adjustedInput":

    --remove last comma
    RIGHT(input,1) == "," ? REPLACE(input,",","") : input
    
    --remove first comma
    FINDSTRING(input,",",1) == 1 ? SUBSTRING(adjustedInput,2,LEN(adjustedInput)) : adjustedInput

    Hope that helps.

     

     


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com

     


    Monday, January 23, 2012 1:47 PM
  • If I'm understanding your requirement correctly, you only want to remove commas if they exist as the first or last character (#1 and #2), but not the comma that actually separates 2 values.  If that's the case, you can use a couple of approaches.  I've broken the expression up into 2 separate derived column expressions so that it's easier to understand, maintain and debug.  Could easily combine the 2 if you'd rather have a single derived column component in your data flow but with a gnarlier expression. 

    Here are the expressions.  I just named the original column "input" and the result of the 1st derived column that feeds the 2nd derived column "adjustedInput":

    --remove last comma
    RIGHT(input,1) == "," ? REPLACE(input,",","") : input
    
    --remove first comma
    FINDSTRING(input,",",1) == 1 ? SUBSTRING(adjustedInput,2,LEN(adjustedInput)) : adjustedInput

    Hope that helps.

     

     


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com

     


    it worked but with slight modifications in the condition....

    Any way, Thank you so much buddy
    • Marked as answer by Prakash.R Monday, January 23, 2012 3:00 PM
    • Edited by Prakash.R Monday, January 23, 2012 3:00 PM
    Monday, January 23, 2012 3:00 PM
  • Prakash -

    Glad you were able to get to a solution.  If my post was part of that solution, please mark it so.  Or mark it as helpful.

    Also, would be appreciated if you would post your final solution to share with the community.  Someone else may have a similar challenge at some point, and samples are always helpful.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com
    • Edited by Brent Greenwood Tuesday, January 24, 2012 2:31 AM
    • Marked as answer by Prakash.R Tuesday, January 24, 2012 8:40 AM
    Tuesday, January 24, 2012 2:31 AM
  • Prakash -

    Glad you were able to get to a solution.  If my post was part of that solution, please mark it so.  Or mark it as helpful.

    Also, would be appreciated if you would post your final solution to share with the community.  Someone else may have a similar challenge at some point, and samples are always helpful.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com

       Hi Brent,

    These are the two conditions i used to remove front and back comma's....this would not be possible without our ideas...Thanks a lot..

    (DT_STR,255,1252)(RIGHT(TRIM(Custom2),1) == "," ? REPLACE(RIGHT(TRIM(Custom2),1),",","") : TRIM(Custom2))

    (DT_STR,255,1252)(SUBSTRING(TRIM(Cust2),1,1) == "," ? REPLACE(SUBSTRING(TRIM(Cust2),1,1),",","") : SUBSTRING(TRIM(Cust2),1,LEN(Cust2)))

    Tuesday, January 24, 2012 8:40 AM