CODEWITHSUNDEEP
sqlsql-server
user3498389
user3498389

Reputation: 123

SQL 2005 Carriage Return in Select Statement resulting in data wrap

Here is my query. 

select
    a.LoanNumber,
    u.Comments,
From
    LoanApp as a
    LEFT JOIN Summary as u on a.id = u.loanapp_id
        inner join temploannumber as t on a.LoanNumber = t.loannumber
order by a.LoanNumber

My issue is Comments field is a text field which contains carriage returns. Query results looks like this:

12345    This is a test

Test failed for this loan.

23456    This is a test. Test was successful.

34567    This is a test.

Test failed for this loan.

Test failed again for this loan.

How can I remove the carriage return just in the select statement to look like the following without data wrap? I do not want to affect the data stored in the database.

12345    This is a test. Test failed for this loan.

23456    This is a test. Test was successful.

34567    This is a test. Test failed for this loan. Test failed again for this loan.

Upvotes: 1

Views: 102

Answers (2)

ACDBA
ACDBA

Reputation: 26

I also have a function that you could use that removes all control characters, but you may not have rights to create functions. I don't remember where I found this code, but I'm sure you could find it somewhere.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 CREATE FUNCTION [dbo].[RemoveNonDisplayChars]

        (@pString VARCHAR(8000))
RETURNS VARCHAR(8000) 
     AS
  BEGIN

--===== Declare Local variables
DECLARE @IncorrectCharLoc SMALLINT, --Position of bad character
        @Pattern          CHAR(37)  --Bad characters to look for

 SELECT @Pattern          = '%['
                          + CHAR(0)+CHAR(1)+CHAR(2)+CHAR(3)+CHAR(4)
                          + CHAR(5)+CHAR(6)+CHAR(7)+CHAR(8)+CHAR(9)
                          + CHAR(10)+CHAR(11)+CHAR(12)+CHAR(13)+CHAR(14)
                          + CHAR(15)+CHAR(16)+CHAR(17)+CHAR(18)+CHAR(19)
                          + CHAR(20)+CHAR(21)+CHAR(22)+CHAR(23)+CHAR(24)
                          + CHAR(25)+CHAR(26)+CHAR(27)+CHAR(28)+CHAR(29)
                          + CHAR(30)+CHAR(31)+CHAR(127)
                          + ']%',
        @IncorrectCharLoc = PATINDEX(@Pattern, @pString)

  WHILE @IncorrectCharLoc > 0
 SELECT @pString          = STUFF(@pString, @IncorrectCharLoc, 1, ''),
        @IncorrectCharLoc = PATINDEX(@Pattern, @pString)
 RETURN @pString
    END

Then just execute the function against it.

select a.LoanNumber, RemoveNonDisplayChars(u.Comments)
From LoanApp as a 
LEFT JOIN Summary as u on a.id = u.loanapp_id 
inner join temploannumber as t on a.LoanNumber = t.loannumber 
order by a.LoanNumber

Good luck!

Upvotes: 1

ACDBA
ACDBA

Reputation: 26

Try this. It replaces newlines and carriage returns with an empty string.

select a.LoanNumber, REPLACE(REPLACE(u.Comments, char(13), ''), char(10), '') 
From LoanApp as a 
LEFT JOIN Summary as u on a.id = u.loanapp_id 
inner join temploannumber as t on a.LoanNumber = t.loannumber 
order by a.LoanNumber

Upvotes: 0

Related Questions