Removing Carriage Returns

Question

Post reply

Removing Carriage Returns

MostInterestingMan

SSCertifiable

Points: 6291
More actions
August 28, 2009 at 1:53 pm

#97981

Good Afternoon,
I have a couple of columns, data types nvarchar and text, with inbedded carriage returns in my .csv data extract. These are causing problems when loading the data as a text file and I need to figure out how to remove them. Any help is greatly appreciated.
Thanks for your time.
Costa,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 15 posts - 1 through 15 (of 17 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Try:

UPDATE tablename

SET columnname= REPLACE(SUBSTRING(columnname, 1, DATALENGTH(columnname)), char(13), '')

WHERE charindex(char(13) , columnname) > 0

HTH!

MJ

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Is the data ending up in the right columns in the database table, or are the carriage returns causing the load to fail?

If I was faced with this problem I'd probably use VB to read the file and remove the carriage returns that occur before the end of the text row. This would take a bit of thinking, but it's doable.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

MANU (8/28/2009)
Try:
UPDATE tablename
SET columnname= REPLACE(SUBSTRING(columnname, 1, DATALENGTH(columnname)), char(13), '')
WHERE charindex(char(13) , columnname) > 0
HTH!
MJ

Manu,

Just curious... Why are you using SUBSTRING in this? REPLACE should be able to do this on it's own. Is it to handle more than 8kb in association with the TEXT datatype?

--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 4

Jeff,

The attempt is to just handle the text or nvarchar datatype.

Thanks,

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

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

Heh... have you or Manu actually tried it on the TEXT datatype to see if it works?

--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 6

I tried and it doesnt work. The extract still has inbedded carriage returns like before with attempts in some records to remove it.

Thanks

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Give this a whirl ...

One of the three following statements or a combination of the two of these statements will work. This depends on whether there are 'newlines', 'carriage returns' olone or 'carriage return/line feeds':

update tablename

set columnname=replace(cast(cast(columnname AS text) AS varchar(max)),char(10),'')

update tablename

set columnname=replace(cast(cast(columnname AS text) AS varchar(max)),char(12),'')

update tablename

set columnname=replace(cast(cast(columnname AS text) AS varchar(max)),char(13),'')

As for efficiency, well ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

Rudy,

varchar(max) is in SQL 2005 so I dont think your code will work.

MJ

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 9

oops ... did in in 2005 - well varchar(8192) will work - then one has to walk through the text ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

vicky-954852 SSC Enthusiast Points: 192 More actions · Answer 10

Hi

Try this

Update tablename set columnname = replace(replace(substring(columnname,1,datalength(columnname)), char(13),''), char(10), ' ')

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

rudy komacsar (9/1/2009)
oops ... did in in 2005 - well varchar(8192) will work - then one has to walk through the text ...

Heh... maybe VARCHAR(8000) or SQL_Variant (fixed at 8016). 🙂

--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 12

vicky (9/2/2009)
Hi
Try this
Update tablename set columnname = replace(replace(substring(columnname,1,datalength(columnname)), char(13),''), char(10), ' ')

REPLACE doesn't work on the TEXT datatype, folks.

--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)

vicky-954852 SSC Enthusiast Points: 192 More actions · Answer 13

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

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 14

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

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]