Removing Carriage Returns

Question

Removing Carriage Returns

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

vicky (9/2/2009)
The replace doesn't work directly to the column, with the substring the sentence works
Create Table #tablename(columnname text)
Insert Into #tablename
Select 'Please post
the contents '
Select * From #tablename
Update #tablename set columnname = replace(replace(substring(columnname,1,datalength(columnname)), char(13),''), char(10), ' ')
Select * From #tablename
Results:
columnname
------------
Please post
the contents
columnname
---------------------------
Please post the contents

Have you tried it on anything that exceeds 8000 characters?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

Carolyn Richardson (9/2/2009)
I use a function and just add in the ASCII character of the item I wish to replace:-
/*-- =============================================
-- Author:Carolyn Richardson
-- Create date: 16th February 2009
-- Description:Clears selected characters from text string
ASCII characters:-
32 = space
9 = tab
10 = New Line
13 = carrige return
-- =============================================================================*/
CREATE FUNCTION [dbo].[fn_ReplaceAsciiChar]
(
@OrigString VARCHAR(255)
)
RETURNS VARCHAR(255) WITH SCHEMABINDING
AS BEGIN
DECLARE @NewString VARCHAR(255),
@Space INT,
@Len INT,
@Ctr AS INT,
@Ctr2 AS INT,
@Char AS VARCHAR(1)
SET @NewString = @OrigString
SET @Len = ( SELECT LEN(@NewString)
)
SELECT @Ctr2 = 1,
@Ctr = 1
WHILE @Ctr <= @Len
BEGIN
SELECT @Char = SUBSTRING(@NewString, @Ctr, 1)
IF ASCII(@Char) IN ( 9,10,13,32 )
BEGIN
SET @NewString = ( SELECT REPLACE(@NewString, @Char, CHAR(32))
)
SELECT @Ctr2 = @Ctr2 + 1
END
SELECT @Ctr = @Ctr + 1
CONTINUE
END
RETURN @NewString
END

Same question here... have you tried it on the TEXT datatype when there's over 8000 characters which is one of the things the OP wants to be able to do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 3

Jeff, Vicky, Carolyn, Rudy and Manu thanks for your input; it is very much appreciated.

set columnname = replace(replace(substring(columnname,1,datalength(columnname)), char(13),''), char(10), ' ') works however I am still having issues for values exceeding 8000 characters. Also I will like to note that the requirements might change in the coming days wherein the client may only want to see the first 3000 characters - in that case this will be the idea solution.

- costa

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends