Solved

Replace function in sql server?

Posted on 2006-11-21
2
234 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

679 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