Re: stripping carrige returns with SQL

If you are trying to replace the carriage return it needs to be \r instead of \ n in your query. Also it may not work because in my experience the replace function under mysql does not replace them in the db but in the query results. If you are looking to replace them permanently you will need a script to do that. I have one if that is what you want to do. David On Fri, 2003-09-26 at 12:50, jeffrey_n_Dyke@Keane.com wrote: > I have a query that is pulling user comments, supplied by via web > internface and creating a text file out of them. In these comments are all > sorts of carrige returns. I've tried stripping them out with the following > queries, but once imported into excel, the carrige returns are still there. > so obviously i'm doing something wrong.... > > I tried doing with the ASCII number .....(no idea if this is valid) > > SELECT Business_Unit.Business_Unit, Category.Category_Name, > REPLACE(Comment_Original, ASCII(10),ASCII(32)) FROM Comment > INNER JOIN > Cat > egory ON Comment.Category_ID = Comment.Category_ID INNER JOIN > Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER > JOIN Business_Unit ON Survey_Response.BUKey = Business_Unit.BUKey WHERE > Comment_Original <> ' ' AND Category.Category_Name IS NOT NULL GROUP BY > Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY > Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE > '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY > ENCLOSED BY '"' lines terminated by '\n'; > > Also with the escaped charatcer. > SELECT Business_Unit.Business_Unit, Category.Category_Name, > REPLACE(Comment_Original, '\n',' ') FROM %µ)Ñ’‰ INNER JOIN Category ON > Co > mment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON > Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_UnitON > Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original <> ' ' > AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, > Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, > Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' > fields terminated by ',' OPTIONALLY ENCLOSED BY '"' lines terminated > by > '\n'; > > Is REPLACE the right SQL Function to use? is there a better way to > determine this character? > > Thanks > Jeff