Extra Loop in Script

[delaap]

I have inherited a script that replaces carriage returns in field with a space. What I really need is a script that checks and updates all fields in a table and not just one field. Does anyone have any ideas how to modify the script to achieve this ?


CREATE PROCEDURE sp_remove_cr AS
DECLARE @str VARCHAR(1000),
@primaryKey VARCHAR(8),
@i INT, @j INT
SELECT @i=COUNT(*) FROM TT_CR_TEST_DATA
SELECT @primaryKey=''
WHILE @i>0
BEGIN
SET ROWCOUNT 1
SELECT @primaryKey=site_id,
@str=Company FROM TT_CR_TEST_DATA
WHERE site_id>@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 TT_CR_TEST_DATA
SET company=@str WHERE site_id=@primaryKey
SELECT @i=@i-1
END
SET ROWCOUNT 0

 

[Inwoner]

UPDATE TT_CR_TEST_DATE SET company = REPLACE(COMPANY,'
',' ');

PS. That this is two lines.

 

[tlbroadbent]

--Update all rows containing a CR - changing CR to space.
UPDATE TT_CR_TEST_DATE
SET COMPANY = REPLACE(COMPANY,char(13),' ')
WHERE charindex(char(13),COMPANY)>0;

Did you really mean all fields (columns) in a table or just the COMPANY column in all rows? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.