Hidden character that creates an extra line

  • 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.

  • 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

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

  • 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

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

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