how to get the multiple substring which is having more than 8000 chars

I want to get use the substring like this. is it any better way to get the substring seperation of a column. i using SQL 2000
thanks in advance
select 
    @p_data1 = SUBSTRING(dv.data, 1, 8000),
    @p_data2 = SUBSTRING(dv.data, 8001 ,10000),
     @p_data3 = SUBSTRING(dv.data, 10001, upto end of the text(16013)),
from
  confirmation l (nolock)
  join version dv (nolock)

Open in new window

renjitkumarAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Try it this way:

select
    @p_data1 = SUBSTRING(dv.data, 1, 8000),
    @p_data2 = SUBSTRING(dv.data, 8001 , 8000),
     @p_data3 = SUBSTRING(dv.data, 16001, 8000)
from
  confirmation l (nolock)
  join version dv (nolock)
0
 
udaya kumar laligondlaTechnical LeadCommented:
no other way is possible as you are using three variables and three ranges. SQL has to check for each range of substring. multiple value return is not possible in substring funciton.
0
 
renjitkumarAuthor Commented:
If i try in that way also i'm getting error "Expression result length exceeds the maximum. 8000 max, 16097 found." when i'm trying to take the value from 8000+.
Ex. if i want to get the data from 8001 to 9000 that time i'm getting this error message.
0
 
udaya kumar laligondlaTechnical LeadCommented:
what is the field type of dv.data. post the full query
0
 
renjitkumarAuthor Commented:
declare @doc_handle int
declare @p_data1 varchar(8000)
declare @p_data2 varchar(8000)
declare @p_data3 varchar(8000)

select
    @p_data1 = SUBSTRING(dv.data, 1, 8000),
     @p_data2 = SUBSTRING(dv.data, 8001 ,10000),
     @p_data3 = SUBSTRING(dv.data, 10001,16097)
from
  confirmation_log dcl (nolock)
  join version dv (nolock)
    on dv.document_id = dcl.document_id

exec sp_xml_preparedocument @doc_handle OUTPUT, @p_data1 +@p_data2 + @p_data3


this is my complete query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.