SQL Server Performance Forum – Threads Archive

varchar(255) wrong words wrapping

One of the colums has data type varchar(255) Let’s say there is string
"I went
to store" in one of the records I need to read it I went to store but on my report the string is being read as "I went
to store" could you please help?

Silly question: The problem is ""?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Sounds like you need to replace the carriage return and and/or line feed characters (CHAR(13) and CHAR(10) respectively) with a blank space. Depending on the client application, you may get CRLF, LFCR, CR or LF, so to cover all possibilities, here’s a catch-all expression:
REPLACE(REPLACE(REPLACE(REPLACE(MyTable.MyColumn, CHAR(13) + CHAR(10), ‘ ‘), CHAR(10) + CHAR(13), ‘ ‘), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘)

Thank you very much Adriaan: this is exacly what I need but one of the word is still separating. Anything else I should try?
Thank you

You’ll need to run a script that reads each character from the problem entry, and do SELECT ASCII() to find out the ASCII value of the break-off character. DECLARE @int INT, @test VARCHAR(255) SELECT @test = mycol FROM mytable WHERE <identify the problem record>
SET @int = 1
WHILE @int <= LEN(@test)
BEGIN
PRINT SUBSTRING(@test, @int, 1) + ‘ – ‘ + CAST(ASCII(SUBSTRING(@test, @int, 1)) AS VARCHAR)
SET @int = @int + 1
END You may need to do a little more research on how to handle nvarchar columns.

Thanks a lot :it did worked.

So what character was causing the line breaks?

]]>