Take out carriage returns on field being exported 1

[Cpreston]

Hi

I have a view which is being used to export data to CSV through SSIS.

When you run the view in SQL the results look fine. The field CALL.TEXT04 however as carriage returns in it and when it comes out looks like below (even with copy and paste it does this). Is there a way I can make it take out the carriage returns and make it just one row of text with perhaps a full stop between them. Thanks

*MUST CHANGE ALL PRICE TO PER METRE*

Shiplap always SG2

*Anything 38mm or 50mm on pse's needs to be 4.2mtr or longer*

 

[ElEye]

Is it as simple as a replace? Something like (REPLACE(CALL.TEXT04,CHAR(13),' ')) to replace a CR with a space?

Dave
[

http://flashspot.com

]

 

[Cpreston]

Hi

Thanks for the reply. yes found the replace command and just currently trying it out. I get back with my results

Thanks agauin

 

[Cpreston]

Hi

I had to use this to make it work (possible more than on CR in the field. Appears to be working now.

REPLACE(REPLACE(dbrderHeader.SpecialInstructions, CHAR(13), ' '), CHAR(10), '')


Thanks

 

[philhege]

Windows line delimiters (or whatever you want to call them) consist of the carriage return (13) and line feed (10) characters. So you're doing the right thing.

-----------
With business clients like mine, you'd be better off herding cats.

 

[fredericofonseca]

no he is not doing the right thing.
Suppose that he is extracting data from a database to be loaded onto a CRM system.
Then suppose that that CR is a line change on a text box. by removing it the text that before was displayed on 2 lines on the old system will now be displayed on a single line.

doing the cleaning of data depends on the what the target system is going to do with it - and from previous posts from the OP this file is going to be loaded by a third party software so only them should be able to decide if the carriage return should be kept on the extract file or not.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886