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.
Msg 8114, Level 16, State 5, Line 34
Error converting data type varchar to bigint.
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
Msg 8114, Level 16, State 5, Line 34
Error converting data type varchar to bigint.
SQLMicrosoft SQL Server
Last Comment
CipherIS
What's the data type for comment column?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Also found this.
https://robbamforth.wordpress.com/2009/02/25/sql-remove-hidden-line-feed-carriage-return-from-a-field/
So after I put my data into a temp table I can use above to delete the carriage returns. Works great.
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)+’%’
So after I put my data into a temp table I can use above to delete the carriage returns. Works great.