Solved

Replace function in sql server?

Posted on 2006-11-21
2
230 Views
Last Modified: 2008-01-09
Dear Experts,

I have write some code in stored procedure.
Why i cant get 4000 chars once it runs replace function.
It only return 3999 chars after it runs replace function.
May i know how to solve this problem where it return 4000 chars after the replace function end?
For your information,
The first print display 4000 chars which is correct.
But after it runs replace functions it left 3999 chars only.

Select @strXML1 = CONVERT(nvarchar(4000), SUBSTRING(XMLData,1,4000)), @strPosition1 = charindex(char(39),xmldata) from tb_batchpocreation_Queue Where QueueID = @QueueID
      
print @strXML1

      If @strPosition1 <> 0
      Begin
            Set @strXML1 =       replace(@strXML1,char(39),char(39)+ char(39))
      End

print @strXML1
0
Comment
Question by:sonny_j81
2 Comments
 
LVL 9

Expert Comment

by:csk_73
ID: 17986145
Hi sonny_j81,

just trying, but try with

replace(@strXML1,nchar(39),nchar(39)+nchar(39)

as strXML1 is an nvarchar I suppose it's better to use nchar...

Hope this helps!

Cesc
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 17986213
try changing datatype of @strXML1 from nvarchar(4000) to nchar(4000)
and also

Select @strXML1 = CONVERT(nvarchar(4000), SUBSTRING(XMLData,1,4000))

to

Select @strXML1 = CONVERT(nchar(4000), SUBSTRING(XMLData,1,4000))
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question