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 Monday, March 3, 2014 12:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960

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).
Post #1547063
Posted Tuesday, March 4, 2014 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 10,206, Visits: 13,152
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

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 #1547341
Posted Tuesday, March 4, 2014 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 12,995, Visits: 12,414
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 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 #1547344
Posted Tuesday, March 4, 2014 9:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960
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
Post #1547401
Posted Tuesday, March 4, 2014 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 12,995, Visits: 12,414
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 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 #1547415
Posted Tuesday, March 4, 2014 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 10,206, Visits: 13,152
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

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 #1547416
Posted Tuesday, March 4, 2014 11:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960
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?
Post #1547466
Posted Tuesday, March 4, 2014 11:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 10,206, Visits: 13,152
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

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 #1547476
Posted Tuesday, March 4, 2014 12:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960
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).
Post #1547486
Posted Tuesday, March 4, 2014 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 12,995, Visits: 12,414
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 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 #1547487
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse