CR + LF In Column Data

I have CR+LF characters in my data and I would like to replace them with nothing. I am trying to us a derived column transformation to get rid of the character but I am getting errors. Here is what I have tried so far:

REPLACE( [Column0],VBCrLf , "")

REPLACE( [Column0],chr(13)+chr(10) , "")

REPLACE( [Column0],char(13)+char(10) , "")

Anyone have any ideas?

Thanks!!!

I'm guessing that

REPLACE( [Column0],\r+\n , "")

will do the job.

Check out the escape characters listed here: http://msdn2.microsoft.com/en-us/library/ms141001.aspx

-Jamie

|||

Thanks for the quick response but that did not work either.

|||

Why not? It really helps to provide error messages in this situation.

-Jamie

|||

Sorry about that. Here is the error message(s).

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Derived Column [711]]: Attempt to parse the expression "REPLACE( [Column0],\r+\n , "")" failed. The token "\" at line number "1", character number "21" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Error at Data Flow Task [Derived Column [711]]: Cannot parse the expression "REPLACE( [Column0],\r+\n , "")". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Derived Column [711]]: The expression "REPLACE( [Column0],\r+\n , "")" on "input column "Column0" (724)" is not valid.

Error at Data Flow Task [Derived Column [711]]: Failed to set property "Expression" on "input column "Column0" (724)".

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

Do I need to enclose the \r+\n with quotes?

Thanks!

|||

DOH! Yes, you need to include quotes. Like this:

REPLACE( [Column0],"\r\n" , "")

Sorry, my mistake.

-Jamie

|||

That worked like a champ!

Thanks a lot for your help Jamie!!!!