substring more than 4000 characters

• kkabdul

  Old Hand

  Points: 326

  More actions

  November 10, 2009 at 2:29 am

  #142892

  i have a data which has 8500 characters and i need the characters from 20 to 8350. how to do?

  substring returns only 4000 characters.

  please help

• Chandru -734144

  Ten Centuries

  Points: 1040

  More actions

  November 10, 2009 at 4:12 am

  #1077149

  select substring(column_name,20,8340) from table_name

  is the above one is not working for you ?

  Thanks,

  Chandru

• andegre

  SSCertifiable

  Points: 5875

  More actions

  November 10, 2009 at 7:37 am

  #1077253

  This is what I use, except it's for text data types. I thought that varchar (if that's what your field is set as) is a max lenght of 4000. Anyway, here's my script to output text that is more than 4000 characters.

  Declare @ptr varbinary(16),

  @length int,

  @Search varchar(200)

  Set@Search = 'Title: 29aa58348'

  --Set@Search = 'Title: 29aa58348'

  Select @length = DataLength(FODocument)

  From Admin..PrintJob

  Where DocumentName = @Search

  Select @ptr = TextPtr(FODocument)

  From Admin..PrintJob

  Where DocumentName = @Search

  --Select @Search As [SearchParameter], @length As [LengthOfField], @ptr As [PointLocation]

  If @ptr IS NOT NULL

  ReadText PrintJob.FODocument @ptr 0 @length

• Chandru -734144

  Ten Centuries

  Points: 1040

  More actions

  November 10, 2009 at 7:45 am

  #1077264

  instead of going for varbinary or something you can use varchar(max).

  Just try out that.

  Thanks,

  Chandru

• Joie Andrew

  One Orange Chip

  Points: 27360

  More actions

  November 10, 2009 at 3:19 pm

  #1077563

  You can use the substring function with text data, so you will not have to deal with text pointers. The only thing is that instead of specifying the offset as the start and length characters, it needs to be specified in bytes.

  http://msdn.microsoft.com/en-us/library/ms187748(SQL.90).aspx

  Joie Andrew
  "Since 1982"