replace () not working on nvarchar(max) column

  • 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).

  • 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 (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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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 (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?

  • 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 (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).

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply