Removing Carriage Returns

[delaap]

Hi,

I'm looking to replace Carriage Returns in a table with a space. Does anyone have an idea of how this can be achieved ?

 

[rajeevnandanmishra]

Hi delaap,
You can do some thing like this.

------------------------
update myTable
set myColumn=CASE charindex(char(13),myColumn)>0
then stuff(myColumn,charindex(char(13),myColumn),1,' ')
else myColumn end

------------------------
This will help if the mycolumn contains only one carriage return. If it contains multiple carriage returns, you have to use a procedure.

Hope this will move you in right direction. If you still face problem, Just ask again. There are so many person to help others.

 

[delaap]

In a nutshell, I have a front end that allows CR's to be inputted, the back end (SQL 2000) allows this also. I need to DTS this data out once a week without carriage returns. On average there are 20/30 CR's across 40 Fields. What I need to do is write a script that cleans all the fields with CR's in them and replaces them with 'Space'. Can you be more specific when you talk about a procedure ?

 

[rajeevnandanmishra]

Hi delaap,
Just go through the following procedure
------------------------------------
CREATE PROCEDURE removeSpace AS
DECLARE @str VARCHAR(1000),
@primaryKey INT (declare it as character if you have a
character type primary key),
@i INT, @j INT
SELECT @i=COUNT(*) FROM myTable
SELECT @primaryKey=0 (initialise it with '' if it is char)
WHILE @i>0
BEGIN
SET ROWCOUNT 1
SELECT @primaryKey=myPrimaryKey,
@str=myColumn FROM myTable
WHERE myPrimaryKey>@primaryKey
SELECT @j=CHARINDEX(CHAR(13),@str)
WHILE @j>0
BEGIN
SELECT @str=STUFF(@str,@j,1,' ')
SELECT @j=CHARINDEX(CHAR(13),@str)
END
UPDATE myTable
SET myColumn=@str WHERE primaryKey=@primaryKey
SELECT @i=@i-1
END
SET ROWCOUNT 0
------------------------------------

This procedure will replace the data for one column you can run the same for multiple columns be creating/executing the dynamic sqls.

Let me know if it does not work.