Hidden character that creates an extra line

Question

Post reply

Hidden character that creates an extra line

diogenes1331

Default port

Points: 1420
More actions
August 2, 2006 at 7:49 am

#114496

In one varchar field I have instances in which there is a hidden character that generates an extra row when I am performing a SELECT statement in Query Analyzer in text mode.

I have been able to isolate the two characters that generate the extra line and they happen to be unicode 13 which is carriage return. But I am not able to find a way to explicitly create a general conditional statement for a REPLACE to avoid the creation of the extra line because I cannot see the characters I need to specify.

I hope I did explain myself.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 1

Prakash Sawant

SSCrazy

Points: 2173

August 2, 2006 at 7:57 am

#652812

If you don't know the exact characters then you can use their ASCII values to find & replace.

Hope this will help.

------------
Prakash Sawant

http://psawant.blogspot.com

David.Poole SSC Guru Points: 75656 More actions · Answer 2

You need to allow for

CHAR(9) = TAB

CHAR(10) = LF (Line Feed)

CHAR(12) = FF (Form Feed)

CHAR(13) = CR

DECLARE 
    @ControlCode VARCHAR(1) ,
    @WhereClause VARCHAR(3)

SET @ControlCode=CHAR(13)
SET @WhereClause='%'+@ControlCode+'%'

UPDATE dbo.YourTable
SET YourStringField=REPLACE(YourStringField,CHAR(13),'')
WHERE YourStringField LIKE @WhereClause

LinkedIn Profile

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

SELECT REPLACE(MyColumn,CHAR(13),'') FROM MyTable

Will remove all instances of the CR character, you may also want to remove the CHAR(10) or LF character as well. For these I would suggest replacing with a space, as in:

SELECT REPLACE(REPLACE(REPLACE(MyColumn,CHAR(13)+CHAR(10),' '),CHAR(13),' '),CHAR(10),' ') FROM MyTable

This way the MyColumn = 'My Test'+CHAR(13)+'string' does not end up as 'My Teststring' as in the 1st example, and handles the fact that you may be seeing the "hard return" CHAR(13)+CHAR(10) or CRLF, CR, or LF.

Andy

diogenes1331 Default port Points: 1420 More actions · Answer 4

Thank you both. David Poole's was a very good start but the CHAR(10) was still there. I then combined the input from both David A Long and David Poole and this is is the SQL I got which at least fixed my problem. I appreciate both your inputs very much.

DECLARE @cc13 VARCHAR(2),@CC10 VARCHAR(2), @W13 VARCHAR(4), @W10 VARCHAR(4)

SELECT @cc13=CHAR(13), @W13='%'+@CC13+'%', @CC10=CHAR(10), @W10='%'+@CC10+'%'

UPDATE MyTable SET MyCol= REPLACE(REPLACE(REPLACE(MyCol,CHAR(13)+CHAR(10),' '),CHAR(13),' '),CHAR(10),' ')

WHERE MyCol LIKE @W13 OR MyCol LIKE @W10