SQL Clone
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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7804 Visits: 3696
In SSMS one of my columns has data like this :

EXEC Proc_gen @PsID=12, @kID=-1, @ID=3183


In SSMS it shows it one record which is how i want,i am applying replace function but i still see spaces between the words. Basically i don't want to see any space between two words in a cell. The datatype on that column is nvarchar(max).
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46599 Visits: 14925
Can you post the code you are using. I just tested using this:

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

SELECT @data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces;



Granted I tested on SQL Server 2012, but I'm not aware of any changes to the REPLACE functionality between 2005 and 2012.

I don't have access to a 2005 server to test on and I don't think I want to spin up a VM just for that.



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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65382 Visits: 17980
Jack Corbett (3/4/2014)
Can you post the code you are using. I just tested using this:

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

SELECT @data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces;



Granted I tested on SQL Server 2012, but I'm not aware of any changes to the REPLACE functionality between 2005 and 2012.

I don't have access to a 2005 server to test on and I don't think I want to spin up a VM just for that.


I ran your code on both 2005 and 2008r2. Much as we both expected, it worked just fine and produced the same results.

_______________________________________________________________

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 Modens 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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7804 Visits: 3696
Jack Corbett (3/4/2014)
Can you post the code you are using. I just tested using this:

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

SELECT @data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces;



Granted I tested on SQL Server 2012, but I'm not aware of any changes to the REPLACE functionality between 2005 and 2012.

I don't have access to a 2005 server to test on and I don't think I want to spin up a VM just for that.


Interesting, is there limit on length of characters on that column? Max length i have is upto 5532 characters
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65382 Visits: 17980
curious_sqldba (3/4/2014)
Jack Corbett (3/4/2014)
Can you post the code you are using. I just tested using this:

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

SELECT @data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces;



Granted I tested on SQL Server 2012, but I'm not aware of any changes to the REPLACE functionality between 2005 and 2012.

I don't have access to a 2005 server to test on and I don't think I want to spin up a VM just for that.


Interesting, is there limit on length of characters on that column? Max length i have is upto 5532 characters


No there is no limit when using MAX datatypes.

http://technet.microsoft.com/en-us/library/ms186862.aspx

_______________________________________________________________

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 Modens 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
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46599 Visits: 14925
I don't think there is any restriction on the # of characters REPLACE will work with as there isn't anything mentioned in BOL.

My best guess is that the spaces you see are not the SPACE character, but may be other white space characters, like tab, and the code I posted won't replace a tab character. So if I have this:

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

SELECT @data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces;



The CHAR(9), tab, is not replaced thus leaving white space between "proc_gen @PsID", while replacing the actual spaces.

Also here's an example that shows that length is not an issue:

DECLARE @data NVARCHAR(MAX) = REPLICATE(CONVERT(NVARCHAR(MAX),'EXEC Proc_gen @PsID=12, @kID=-1, @ID=3183'), 10000);

SELECT
@data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces,
LEN(@data) AS stringLength,
DATALENGTH(@data);





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
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7804 Visits: 3696
Jack Corbett (3/4/2014)
I don't think there is any restriction on the # of characters REPLACE will work with as there isn't anything mentioned in BOL.

My best guess is that the spaces you see are not the SPACE character, but may be other white space characters, like tab, and the code I posted won't replace a tab character. So if I have this:

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

SELECT @data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces;



The CHAR(9), tab, is not replaced thus leaving white space between "proc_gen @PsID", while replacing the actual spaces.

Also here's an example that shows that length is not an issue:

DECLARE @data NVARCHAR(MAX) = REPLICATE(CONVERT(NVARCHAR(MAX),'EXEC Proc_gen @PsID=12, @kID=-1, @ID=3183'), 10000);

SELECT
@data AS theData ,
REPLACE(@data, ' ', '') AS dataNoSpaces,
LEN(@data) AS stringLength,
DATALENGTH(@data);



You are right, i think they are tab delimited. Any thoughts on getting rid of tab delimiters?
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46599 Visits: 14925
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.



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
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7804 Visits: 3696
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).
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65382 Visits: 17980
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?

_______________________________________________________________

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 Modens 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)
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