January 22, 2010 at 12:29 pm
Hi everyone.
I'm having a problem exporting a view to a text file. There is a column in my view called "csm_description", and it's required to be 255 characters in the text file - so the view has the following data behind it: CONVERT(varchar(255), cm.csm_description) AS 'buildingpermitdescription',
Everything works beautifully except when there is a carraige return in the description text. What happens is when the text file is created, the carraige return is added to the text file, and throws everything off. I tried the REPLACE function (to go along with the CONVERT function), but it doesn't seem to be working correctly. It's just adding additional blank characters to the text file (too many to count).
REPLACE(CONVERT(varchar(255), cm.csm_description), CHAR(13), '') AS 'buildingpermitdescription',
I also tried using the CONVERT first, but got an error that tells me "Argument data type text is invalid for argument 1 of replace function." Argh.
CONVERT(varchar(255), (REPLACE(cm.csm_description, CHAR(13), ''))) AS 'buildingpermitdescription',
I'm apparent that I'm missing something obvious (and losing my hair as well). Any ideas? Thanks.
January 22, 2010 at 2:13 pm
I found some old code I used to get rid of CR_LF in the data.
update MyTable
set cm.csm_description = replace(replace(cm.csm_description, char(13), ''), char(10), '')
where charindex(char(10), cm.csm_description) > 0
or charindex(char(13), cm.csm_description) > 0
January 22, 2010 at 4:41 pm
After some digging - this seemed to finally do the trick.
left (replace(replace(convert (char(255), cm.csm_description), char(13), ' '), char(10), ' '), 255) AS 'buildingpermitdescription'
I realized that the field was a text field, hence the "left" function. Thanks for your help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply