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.
	CASE comment
	WHEN CHARINDEX(CHAR(10), comment) Then
		'True 10'
	WHEN CHARINDEX(CHAR(13), comment) Then
		'True 13'
	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?
Avatar of ste5an
Flag of Germany image

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


Also found this.

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.