Sean Lange (3/4/2014)

---

curious_sqldba (3/4/2014)

---

Sean Lange (3/4/2014)

---

curious_sqldba (3/4/2014)

---

Sean Lange (3/4/2014)

---

curious_sqldba (3/4/2014)

---

Jack Corbett (3/4/2014)

---

Issues with different white space characters is a pain. You just need to use the CHAR/NCHAR function in the REPLACE, like this:

DECLARE @data NVARCHAR(MAX) = 'EXEC Proc_gen' + CHAR(9) + '@PsID=12, @kID=-1, @ID=3183';

SELECT @data AS theData ,

REPLACE(CHAR(9), REPLACE(@data, ' ', '')) AS dataNoSpaces;

This one does both the Tab and the space.

The replace function requires 3 argument(s).

Easy enough.

DECLARE @data NVARCHAR(MAX) = 'EXEC Proc_gen' + CHAR(9) + '@PsID=12, @kID=-1, @ID=3183';

SELECT @data AS theData ,

REPLACE(CHAR(9), REPLACE(@data, ' ', ''), '') AS dataNoSpaces;

That makes me wonder. If you can't figure that out do you understand what this is doing?

Well, i actually copied first portion of your first reply and then copied bottom half from second reply, so yeah that's why i was getting wrong result. I should have given more details.

I have 500 records each record is like the one i gave in example, and each space is a tab delimited, there can more than one tab delimited in each record.

The REPLACE function doesn't replace the first instance of the string, it replaces ALL instances in the entire string in one fell swoop.

I get that, but i don't want to be doing this 'DECLARE @data NVARCHAR(MAX) = 'EXEC Proc_gen' + CHAR(9) + '@PsID=12, @kID=-1, @ID=3183';' for every record.

I have copy pasted two records into notepad attached here. In SSMS these appear as two records, but when i copy paste in excel, each tab takes one record. I just want these two as two records in excel like i see in SSMS.

You don't have to. That declaration is because Jack doesn't have the table with that data in it. Just put the column name there instead of a variable.

You might notice that Jack had char(9) in the wrong location in his code.

Here is how you would do this with a table.

create table #Something

(

SomeValue varchar(max)

)

insert #Something

select

'EXEC PGn_1_0

@PID=1,

@TkID=-1,

@UrID=318,

@PageIndex=1,

@PageSize=15,

@Condition=N''((AtDate >= CONVERT(nvarchar(20), DATEADD(d, -(DAY(@Today)-1), @Today), 101) AND AdDate < CONVERT(nvarchar(20), DATEADD(m, 1, DATEADD(d, -(DAY(@Today)-1), @Today)), 101))'',

@SortColumn=NULL,

@SortOrder=NULL'

insert #Something

select

'EXEC PG_1_0

@PID=12,

@TkID=-1,

@UID=313,

@PageIndex=1,

@PageSize=15,

@Condition=N''((ADate >= CONVERT(nvarchar(20), DATEADD(d, -(DAY(@Today)-1), @Today), 101) AND AdDate < CONVERT(nvarchar(20), DATEADD(m, 1, DATEADD(d, -(DAY(@Today)-1), @Today)), 101)) '',

@SortColumn=NULL,

@SortOrder=NULL'

select * from #Something

SELECT SomeValue AS theData ,

REPLACE(REPLACE(SomeValue, ' ', ''), CHAR(9), '') AS dataNoSpaces

from #Something

drop table #Something