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.
hi
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
from you_table