Solved

Replace function in sql server?

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now