Getting error "String or binary data would be truncated." when REPLACE is used for NVARCHAR data type.

Question

Hi,

I have searched for the solution for Replace function on many forums, but did not find any. I want to replace an NVARCHAR string having length more than 4000 characters (i.e. 8000). Has Microsoft found any solution for Replace function?

If I use VARCHAR in place of NVARCHAR, non-English characters are not getting displayed properly.

Thanks in advance.

Answer 1

Sorry, not tested

SELECT CASE WHEN LEN(col) <4000 THEN REPLACE(....) END  FROM tbl

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

declare @t table ( s nvarchar(max) collate Latin1_General_CI_AS )
insert into @t
select replicate(N'W', 2000)
union all
select replicate('Й' collate Latin1_General_CI_AS , 8000)
union all
select replicate(N'Й' collate Latin1_General_CI_AS , 8000)

select replace( s, N'Й', N'Б' ) 
  from @t

---

http://www.t-sql.ru

Answer 3

I'm not sure that I understand what your problem is. Can you give an example of what's bothering you?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

declare @v1 nvarchar(max), @v2 nvarchar(max), @v3 nvarchar(max), @repl nvarchar(max)

set @v1 = 'abc' + replicate(N'0', 8000)
set @v2 = replicate('Й' , 4000)
set @v3 = replicate(N'Й' , 8000)
set @repl = replace(@v1, @v2, @v3)
select @repl as repl, LEN(@v1) as v1, LEN(@v2) as v2 , LEN(@v3) as v3

Above executes successfully.

But with this change:

set @v2 = replicate('Й' , 4001)

I get this error: "String or binary data would be truncated."

---

Puja

Answer 5

Tested on SS2005 SP2,SP3 worked just fine (NO ERROR)

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

I am using SQL Server 2008. Though for below change:

set @v2 = replicate('Й' , 4001)

it did not work for me on SQL server 2005, SP2 also.

---

Puja

Answer 7

I am using SQL Server 2008. Though for below change:

set @v2 = replicate('Й' , 4001)

it did not work for me on SQL server 2005, SP2 also.

---

Puja

set @v2 = replicate( cast( N'Й' as nvarchar(max) ) , 4001)

???

---

http://www.t-sql.ru

Answer 8

If you make it UNICODE string literal, it does not give an error:

 declare @v1 nvarchar(max), @v2 nvarchar(max), @v3 nvarchar(max), @repl nvarchar(max)
 set @v1 = 'abc' + replicate(N'0', 8000)
 set @v2 = replicate(N'Ő' , 4001)
 set @v3 = replicate(N'Ő' , 8000)
 set @repl = replace(@v1, @v2, @v3)
 select @repl as repl, LEN(@v1) as v1, LEN(@v2) as v2 , LEN(@v3) as v3

Related article:

http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 9

How to explain this in a hurry? (I've a meeting in three minutes?

But the root problems seems to be that replace accepts an argument that is nvarchar(4000) for the second and third argument. Thus, you get a truncation when you pass 4001 characters, and this raises an error and does not happen silently.

It works when you say:

set @v2 = replicate(N'?' , 4001)

Since in this case @v3 is truncated to 4000 characters. This is because replicate returns the same type as the input. Try:

set @v2 = replicate(convert(nvarchar(MAX), '?'), 4001)

and you get the error message.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

The scenario is similar to below example:

DECLARE @a NVARCHAR(MAX)
DECLARE @b nvarchar(max)

select @b = REPLICATE('a', 4000)
/*select @b = @b + '123412341234'*/
SET @a = N'TEst: #abc# :'
SET @a = REPLACE(@a , CONVERT(NVARCHAR(max),N'#abc#'), @b)
PRINT 'this line does not fail'
SET @a = CONVERT(NVARCHAR(max), REPLACE(@a , @b, CONVERT(NVARCHAR(max),N'#abc#')))

PRINT '------------------'
select @b = REPLICATE('a', 4000)
select @b = @b + '123412341234'
SET @a = N'TEst: #abc# :'
SET @a = REPLACE(@a , CONVERT(NVARCHAR(max),N'#abc#'), @b)
PRINT 'this line fails'
SET @a = CONVERT(NVARCHAR(max), REPLACE(@a , @b, CONVERT(NVARCHAR(max),N'#abc#')))

from

http://msdn.microsoft.com/en-us/library/ms186862%28v=sql.100%29.aspx

It fails for both 2005 & 2008.

---

Puja

Answer 11

What is the solution for replacing string more than 4000 for NVARCHAR ?

---

Puja

Answer 12

May be you can try this

 

SET @a = CONVERT(NVARCHAR(max), REPLACE(@a , cast(@b as nvarchar(4000)), CONVERT(NVARCHAR(max),N'#abc#')))

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 13

May be you can try this

 

SET @a = CONVERT(NVARCHAR(max), REPLACE(@a , cast(@b as nvarchar(4000)), CONVERT(NVARCHAR(max),N'#abc#')))

This does not work. 2nd expression will be truncated, which throws error.

---

Puja

Answer 14

Works for me in SQL 2012.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

We work on SQL Server 2008, is there any workaround?

---

Puja

Answer 16

This scenario fails in SQL 2008 R2 and in SQL 2012.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 17

The Reason i think is that the replace function does not know the length of the @b variable at the compile time and assume that it will have the length of 4000 at max for nvarchar (8000 in case of varchar).

I tried the code with replacing @b with a constant and was able to run it successfully.

deducing this from : http://connect.microsoft.com/SQLServer/feedback/details/398684/sql-server-2008-string-function-replace-does-not-return-the-correct-value-as-indicated-in-bol-when-using-varchar-as-the-data-type#details

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 18

Also, I believe we did discuss this problem before. It looks like there is a bug in the replace function (as it's not documented in BOL). There should be connect items about this problem.

Try searching this forum, I'm pretty sure we ran into that problem few months ago.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 19

I have checked for link given by "Rishabh K", it says its Microsoft documentation issue or programming issue. But there is no workaround.

Can anyone please give the link for solution?

---

Puja

Answer 20

Not sure about the solution but i tried passing it as constant instead of variable and it worked

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 21

What is the solution for replacing string more than 4000 for NVARCHAR ?

You would need to break it up in pieces. That is, first replace the first 4000 characters, then the next 4000 characters etc.

Or you could write you function in .Net for the task.

It is quite apparent from the examples, that the second and third parameters for replace() are nvarchar(4000)/varchar(8000).

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 22

It is not documented in BOL.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 23

Let me try for breaking down the string into 4000 characters as suggested by Erland Sommarskog.

---

Puja

Answer 24

What are you trying to do? What is the business goal? What is the database design?

It is very unusual to do such large string manipulations.

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 25

I am trying to do something like this:

DECLARE @a NVARCHAR(MAX)
DECLARE @b NVARCHAR(max)

select @b = REPLICATE('a', 8000)
select @b = @b + '12344656899'
SET @a = N'TEst: #abc# :'
SET @a = REPLACE(@a , CONVERT(NVARCHAR(max),N'#abc#'), @b)
--PRINT 'this line fails'
--SET @a = CONVERT(NVARCHAR(max), REPLACE(@a , @b, CONVERT(NVARCHAR(max),N'#abc#')))
--select LEN(@a), LEN(@b)

declare @t_cnt int, @cnt1 int, @rem int
if LEN(@b) > 4000
begin
    select @t_cnt = (LEN(@b)/4000) + 1
    set @cnt1 = 1
    set @rem = LEN(@b)
    while @cnt1 <= @t_cnt
    begin

        if @rem > 0 and len(@rem) = 4
        begin
            --print @cnt1
            SET @a = REPLACE(@a , SUBSTRING(@b, 1 + (4000 * (@cnt1 -1)), (4000 * @cnt1)), CONVERT(NVARCHAR(max),N'#abc#'))
            --select 'if',@cnt1 as cnt, 1 + (4000 * (@cnt1 -1)) as from1, (4000 * @cnt1) as to1, @rem as rem, len(@rem)
        end
        else
        begin
            --print @cnt1
            SET @a = REPLACE(@a , SUBSTRING(@b, 1 + (4000 * (@cnt1 -1)), LEN(@b)), CONVERT(NVARCHAR(max),N'#abc#'))
            --select 'else',@cnt1 as cnt, 1 + (4000 * (@cnt1 -1)) as from1, len(@b) as to1, @rem as rem, len(@rem)
        end
        set @rem = @rem  - 4000        
        set @cnt1 = @cnt1 + 1
    end

end        

But I found 1 issue that string to be replaced will get replaced more number of times than required.

The application in which I need string manipulation creates xml dynamically from existing xml, out of which only 1 parent node will be searched for replacing some calculated data in parent node.

Here, string that is being searched is more than 4000 Unicode characters, even if I don't consider Unicode string and use it as VARCHAR, then also this issue will come for varchar string length > 8000, i.e. max limit.

---

Puja

Answer 26

May be you don't want to do such manipulations in T-SQL?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 27

Why so? Is there any other way?

---

Puja

Answer 28

T-SQL is not the best language for complex string manipulations. You can get better results using .NET languages or CLR function.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 29

We are using JAVA with SQL server 2008. If such string manipulations are done on UI side, I think performance will degrade.

---

Puja

Answer 30

Is there any service pack fix for this issue?

---

Puja

Answer 31

I've tested this code in SQL Server 2008 R2 SP1 and in SQL Server 2012 RTM. In both I got the error about string or binary data truncated. I don't think there is a fix inside SQL Server for this problem (in best case scenario we can expect documentation fix). So, IMHO the solution needs to be in .NET and it can be a CLR function or CLR stored procedure.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 32

I don't think these large string manipulations work seamlessly in T-SQL. Prove me wrong.

The problem starts right on the second line:

declare @v1 nvarchar(max), @v2 nvarchar(max), @v3 nvarchar(max), @repl nvarchar(max)
set @v1 = 'abc' + replicate(N'0', 8000)
select LEN (@v1)
-- 4000 

set @v1 = CONVERT(nvarchar(max),'abc') + replicate(CONVERT(nvarchar(max),N'0'), 8000)
select LEN (@v1)
-- 8003

Related article:

http://www.sqlusa.com/bestpractices/pad/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 33

You are right Kalman Toth.

When string is replaced for 2nd loop, it gives same truncation error :(

Replacing string in parts is working fine for me when Unicode string length is < 8000.

SET @a = REPLACE(@a , SUBSTRING(@b, 4001, 8000), CONVERT(NVARCHAR(max),N'#abc#'))

What is the issue here?

Limitation for 2nd expression in Replace is for 4000 characters, but here its 3999 characters.

---

Puja

Answer 34

I got the issue. It should be:

SET @a = REPLACE(@a , SUBSTRING(@b, 1 + (4000 * (@cnt1 -1)), 4000), CONVERT(NVARCHAR(max),N'#abc#'))

3rd expression is length, which was wrong.

---

Puja

Answer 35

It is not documented in BOL.

It might be in the future:

https://connect.microsoft.com/SQLServer/feedback/details/737386/the-topic-for-replace-fails-to-specify-the-data-types-for-the-arguments

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 36

The application in which I need string manipulation creates xml dynamically from existing xml, out of which only 1 parent node will be searched for replacing some calculated data in parent node.

Maybe then you should manipulate the XML document with XQuery? (Which i supported in SQL Server.)

Without knowing the full story, it's hard to tell, but as other have suggested, you may be using the wrong tool for the task.

I can think of these possibilities:

1) XQuery.
2) A CLR function in SQL Server that performs the string manipulation.
3) A CLR function that uses XML classes to manipulate the document.
4) Handle it in the middle-layer of your application.

One thing to keep in mind that in the general case, it is not really possible to use pure string replacement to change an XML document. Text may be enticised, and you may replace the wrong things and cause a mess.

I think you seriously need to consider other solutions. You may not be versed in XQuery or C# programming, but just because you only can hold a hammer does not mean that everything encounter is a nail.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 37

Thank you all for your support and suggestion.

I found the solution from :

http://beyondrelational.com/ask/public/questions/258/xquery-unable-to-resolve-sqlvariablevariable-the-variable-must-be-declared-as-a-scalar-tsql-variable.aspx

Though replacing in parts worked for me, but its not perfect solution. So I have converted nvarchar data to xml and used modify method.

Thank you once again.

---

Puja

Answer 38

I found the solution from :

http://beyondrelational.com/ask/public/questions/258/xquery-unable-to-resolve-sqlvariablevariable-the-variable-must-be-declared-as-a-scalar-tsql-variable.aspx

Though replacing in parts worked for me, but its not perfect solution. So I have converted nvarchar data to xml and used modify method.

That sounds like a more sustainable solution. Although it should be admitted that the .modify method is nothing I use without looking a lot in the manual and at examples.

---

Erland Sommarskog, SQL Server MVP, [e-mail]