Link to home
Create AccountLog in
Avatar of CipherIS
CipherISFlag for United States of America

asked on

SQL Find Carriage Return and Delete it.

Trying to write an SQL Script that will find a carriage return and delete it.  When I export the data to excel the carriage return is causing data to go to the next line and into the first field.
SELECT *,
	CASE comment
	WHEN CHARINDEX(CHAR(10), comment) Then
		'True 10'
	WHEN CHARINDEX(CHAR(13), comment) Then
		'True 13'
	ELSE
		'False'
	END AS CheckReturn 
FROM #Temp

Open in new window

Attempting to run the above code I receive error:

Msg 8114, Level 16, State 5, Line 34
Error converting data type varchar to bigint.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

What's the data type for comment column?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of CipherIS

ASKER

Also found this.

https://robbamforth.wordpress.com/2009/02/25/sql-remove-hidden-line-feed-carriage-return-from-a-field/

delete from TABLE
where FIELD1 like ‘%’+CHAR(13)+’%’

delete from TABLE
where FIELD1 like ‘%’+CHAR(10)+’%’

Open in new window


So after I put my data into a temp table I can use above to delete the carriage returns.  Works great.