Before it was okay, never got this issue in my machine but don't now since when I am getting this issue. I guess some setting is changed in SQL. when I copy ad hoc query result to any other tool like excel, word, outlook, notepad, it splits in several rows. basically it splits after every double spaces. I have reinstalled sql and ms office as well. but still same issue. it would be great help if someone give the solution. hanging on this since a week.
Line Split Issues when Copying Data from sql to any other tool like excel, word, outlook, notepad etc
please check the following link .. hope it helps
Thank you for the suggestion. yeah I had checked with this as well. and got this error msg: Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.
In other hand this issue came in scene since couple of days only. that means it was alright before while i am working with same kind of code. and when I tried the same thing in my coworker's machine it is totally fine no line splits there.
and same issue with mysql as well. definitely it is setting issue of my laptop but don't know how to reset it.
You can use this scalar function, execute the code below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create FUNCTION [dbo].[RemoveSpecialChars] ( @string varchar(200) ) RETURNS varchar(200) with returns null on null input AS BEGIN declare @byte int While 1=1 begin set @byte = PATINDEX('%[^0-9A-Za-z]%',@string) if @byte = 0 break; set @string = STUFF(@string,@byte,1,'') end RETURN @string END
Then in your SQL query use it like so :-
Select [dbo].[RemoveSpecialChars](ADD_Field_Name_Here) as Column1