Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

replace () not working on nvarchar(max) column Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 12:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,283, Visits: 2,959
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.
Post #1547488
Posted Tuesday, March 4, 2014 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 13,268, Visits: 12,097
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.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1547493
Posted Tuesday, March 4, 2014 12:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,283, Visits: 2,959
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.


  Post Attachments 
SampleOutput.txt (3 views, 635 bytes)
Post #1547499
Posted Tuesday, March 4, 2014 12:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 13,268, Visits: 12,097
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



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1547510
Posted Tuesday, March 4, 2014 12:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,283, Visits: 2,959
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



Thank you taking time in replying.

So if you copy paste the ouput in excel you will see that just first two words are together and all the other one's still have tab delimiters and they show up in different lines.
Post #1547513
Posted Tuesday, March 4, 2014 1:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 13,268, Visits: 12,097
curious_sqldba (3/4/2014)


Thank you taking time in replying.

So if you copy paste the ouput in excel you will see that just first two words are together and all the other one's still have tab delimiters and they show up in different lines.


No there are no TABS left if you run that code.

The new lines are not TABS that are carriage returns (char(13)).

SELECT  SomeValue AS theData ,
REPLACE(REPLACE(SomeValue, ' ', ''), CHAR(9), '') AS dataNoSpaces,
charindex(char(9), REPLACE(REPLACE(SomeValue, ' ', ''), CHAR(9), '')) AS FirstLocationOfTab,
charindex(char(13), REPLACE(REPLACE(SomeValue, ' ', ''), CHAR(9), '')) AS FirstLocationOfCarriageReturn
from #Something




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1547516
Posted Tuesday, March 4, 2014 1:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 11,217, Visits: 12,974
As Sean has said, they aren't tabs, they are carriage returns (CHAR(13)) If they were tabs you wouldn't get multiple rows, you'd get multiple columns in excel.

There are many different white space characters and when you aren't getting the results you need you need to find out what character is really there. You can use a numbers/tally table to break out a string into each character and use ASCII([character]) to find out the ASCII code for each character and then replace the codes you get for white space. I use http://www.asciitable.com/ when I'm wondering what an ASCII code stands for.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1547524
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse