none
removing tabs etc from the Derived Col Transform RRS feed

Answers

  • Can you try the following expression in the Derived Column editor:

     

    REPLACE(colOmega,"\x0009","")  
     


    colOmega is your input column.

    Let us know if works.


    Can you also try "\t" ?

    Thanks.
    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    • Marked as answer by Bob Bojanic Thursday, January 29, 2009 7:16 PM
    Thursday, January 22, 2009 7:57 PM

All replies

  • Hi,

    I haven't tried this but SQL Server recognises \t as a tab sequence so I wonder if you could use it.

    Cheers
    Jeff
    SQL Server MVP WARDY IT Solutions, Solutions Architect
    Thursday, January 22, 2009 11:35 AM
  • The following solutions work in SQL Server 2008 T-SQL. Can you try it in SSIS?  Let us know what happens.

    DECLARE @String varchar(64) = 'New York City is'+char(9)+'on Manhattan island'  
    SELECT @String,   
            REPLACE(@String, CHAR(9), ''),  
            REPLACE(@String, 0x09,'')  
              
    GO  
     
    /* Results  
     
    New York City is on Manhattan island      
    New York City ison Manhattan island   
    New York City ison Manhattan island  
    */ 

    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Thursday, January 22, 2009 2:00 PM
  • thanks guys, but I'm looking for an expression (or maybe vb script) based solution because this is the kind of thing I like to do in the buffer instead of the db engine.

    I did see a partial regular expression solution somewhere yesterday, baked into a vb script.  It removes hex zero from each row.  It might have some promise if extended somewhat, not sure yet.   I'll also be looking for what expression represents hex 09 , perhaps because the derived column transform could be enlisted to do this for me. 
    Thursday, January 22, 2009 3:13 PM
  • Don't know if this will help, but there are a couple "Regex" transforms freely available:

    One from Microsoft: Regex
    Two from SQLIS.com: RegexClean and RegularExpressionTransformation

    Todd McDermid's Blog
    Thursday, January 22, 2009 5:26 PM
    Moderator
  • No problem.

    The original question was in relation to using the Derived Column Transformation Task and how to strip out tabs.

    Cheers
    Jeff
    SQL Server MVP / WARDY IT Solutions, Solutions Architect
    Thursday, January 22, 2009 6:27 PM
  • Can you try the following expression in the Derived Column editor:

     

    REPLACE(colOmega,"\x0009","")  
     


    colOmega is your input column.

    Let us know if works.


    Can you also try "\t" ?

    Thanks.
    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    • Marked as answer by Bob Bojanic Thursday, January 29, 2009 7:16 PM
    Thursday, January 22, 2009 7:57 PM
  • sorry Jeff, when you said SQL Server, I thought you were talking about T-SQL, not the transform component's expression language.  I'll try your's and SQLUSA's ideas and post results here.  I'll also look at Todd's stuff.  The sad thing here is that my flat file has over 130 columns coming in.   I'm starting to think/hope that it will be really nice if all columns are in some kind of .net collection that a foreach loop can act on from inside a vb script.  
    Friday, January 23, 2009 6:13 PM
  • Hello,

    I'm experiencing the same issue where I cannot get the Derived Column tranformation in SSIS 2008R2 to recognize the Hex(9) or "\t" expressions for a TAB value.  The Flat File Destination only recognizes both as literals, i.e. 9 or \t, respectively.  And the Derived Column transformation even accepts Hex(9) as a legitimate function but transposes the integer argument (9) to 9 instead of TAB.

    Has anyone successfully created a TAB value within SSIS transformations?

    Thanks,

    Ben

    Wednesday, October 13, 2010 9:27 PM
  • Hi Ben.  Should we unmark this as answered?  It sounds like you are saying that you tried  REPLACE(colOmega,"\x0009","")   and it did not work.   I cannot recall why I never revisited this post.
    • Edited by db042188 Thursday, October 14, 2010 8:26 PM spelling
    Thursday, October 14, 2010 8:25 PM
  • This solution worked for me. I was dealing with a vertical tab (New Line Character) that I had to remove: -- CODE -- TRIM(REPLACE(ColumnName,"\r\n"," ")) It's important to note what kind of data you're working with: Vertical tab, Horizontal tab etc. I imported the data as is into SQL, and then using the ASCII T-SQL function determined what character I was dealing with. I then found the following link explaining what the ASCII value was that I found (13), however it didn't tell me how to deal with it in SSIS: http://www.csgnetwork.com/asciiset.html I then found the solution on MSDN but closed the site before I could mark the person's answer as correct. The following link explains literals in SSIS (I found this by Googling it): http://msdn.microsoft.com/en-us/library/ms141001.aspx I hope this helps.
    Tuesday, May 24, 2011 7:57 AM
  • Both "\x0009" and "\t" worked or me.

    Thanks!
    Thursday, September 15, 2011 3:27 PM