Remove CRLFs in file

• pkrieg

  Mr or Mrs. 500

  Points: 538

  More actions

  August 11, 2006 at 10:11 am

  #114080

  I have a need to remove all CRLFs from a file in my SP.

  How can I do this?

  Thanks in advance.

  PK

   

   

• brendt hess

  Hall of Fame

  Points: 3256

  More actions

  August 11, 2006 at 3:01 pm

  #654674

  Unfortunately, this question is ambiguous. If you mean:

  "How can I remove al CRLFs from a COLUMN in my TABLE"

  or

  "How can I remove al CRLFs from a COLUMN in the output from my Stored Proc"

  then an answer can be given.

  For the first, you would perform an UPDATE statement, like this:

  UPDATE MyTable

  SET MyColumnWithCRLFs = Replace(MyColumnWithCRLFs, CHAR(13) + CHAR(10), ' ')

  NOTE: I replaced the CrLf values with a space. This prevents wrapped lines like:

  I think that Billy

  is a Poo-poo head

  from becoming

  I think that Billyis a Poo-poo head (who is Billyis?)

  For the second question, use the same sort of construct in your SELECT statement within the Stored Proc.

• pkrieg

  Mr or Mrs. 500

  Points: 538

  More actions

  August 11, 2006 at 5:29 pm

  #654688

  Thanks for the reply but here is what I currently have in my SP and is is putting a CRLF after each row.

  What I need is either during this bcp remove CRLFs which I don;t think I can do

  OR

  after this bcp completes fire up something to remove them in the file.

  Thanks,

  PK

  declare @DBFileName nvarchar(250)

  set @DBFileName = '"select substring(h837xsplit,22,250) from ' + DB_NAME() + '..h837x2split order by substring(h837x2split,1,21)"'

  declare @FileName nvarchar(250)

  /*  @PathName and @FileNamex were previously set  */

  set @FileName = @PathName + @FileNamex

  declare @BCP nvarchar(250)

  set @BCP = 'bcp ' + @DBFileName + ' queryout ' + @FileName + ' -c'

  exec master..xp_cmdshell @BCP