Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


replace () not working on nvarchar(max) column


replace () not working on nvarchar(max) column

Author
Message
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 3573
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16674 Visits: 17033
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)
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 3573
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.
Attachments
SampleOutput.txt (3 views, 635 bytes)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16674 Visits: 17033
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)
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1545 Visits: 3573
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16674 Visits: 17033
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)
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11054 Visits: 14859
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search