SQLTeam.com | Weblogs | Forums

Line Split Issues when Copying Data from sql to any other tool like excel, word, outlook, notepad etc

sql2012

#1

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.


#2

hi

please check the following link .. hope it helps :slight_smile: :slight_smile:


#3

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.


#4

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