Replace CRLF with Space - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Replace CRLF with Space

Author

Topic

BAHill
Starting Member

3 Posts

Posted - 2004-05-03 : 14:31:26

I am developing a web application that allows users to cut and paste information displayed in a table into Microsoft Excel. The application that writes the information to the database (beyond my control) allows users to include CRLF characters. When the data is cut and pasted to Excel it shows multiple lines for a single entry due to the CRLF characters. I want to replace the CRLF with a space, preventing the Excel issue. Here is my code, can anyone help me understand why it is not working?

SELECT ISNULL(REPLACE(REPLACE(Comment,CHAR(ASCII(10)),CHAR(ASCII(32))),CHAR(ASCII(13)),CHAR(ASCII(32))),'None') 'Comment'

Thanks,

BAHill
bahill@oceanspray.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 14:38:01

Could you show us some sample data? If you gave us an example like this, we'll better be able to help you:

DECLARE @String VARCHAR(7000)

SET @String = 'Tara was here.' + CHAR(13) + 'Bye.'

PRINT @String

SET @String = ISNULL(REPLACE(REPLACE(@String, CHAR(13), ' '), CHAR(10), ' '), 'None')

PRINT @String

Tara

BAHill
Starting Member

3 Posts

Posted - 2004-05-03 : 14:42:53

Here is the code (testing...sure you don't want the whole big stored procedure).

SELECT ISNULL(REPLACE(REPLACE(Comment,CHAR(ASCII(10)),CHAR(ASCII(32))),CHAR(ASCII(13)),CHAR(ASCII(32))),'None') 'Comment'
FROM Production.dbo.Downtime DT
WHERE DT.RecID = '302500'

I am using RecID 302500 because I know it contains a CRLF.

Reurns:

LOOSE BOTTLES
WEBBING CAME OFF DRUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 14:45:06

I don't know what the 'Comment' at the end is for?

Use this instead:

SELECT ISNULL(REPLACE(REPLACE(Comment, CHAR(13), CHAR(32)), CHAR(10), CHAR(32)), 'None')
FROM Production.dbo.Downtime DT
WHERE DT.RecID = '302500'

That gets rid of CHAR(13), CHAR(10), and if the column is NULL, it'll change it to None. What else do you need to search for?

Tara

BAHill
Starting Member

3 Posts

Posted - 2004-05-03 : 14:49:03

Thank you very much, that worked perfectly!

P.S. the 'Comment' at the end is like saying AS Comment. Gives my column a friendly name for retrieval in my .NET apps!

Thanks,

BAHill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources