Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012

    Question Unanswered: Substring a TEXT type field

    Is there anyway to take a TEXT type Column and get the first 32768 characters from it? The SUBSTRING command only works on CHAR and VARCHAR2. LEN works on CHAR, VARCHAR2 and TEXT. I want to process all TEXT fields in my application but only the first 32768. Right now I must ignore any text field over 32768. Orcale has DBMS_LOB sytem functions to work with CLOBs. DOes Sybase have anything similar?


  2. #2
    Join Date
    Oct 2012
    Hello Jim,

    A way of displaying only a part from a text field is using readtext :
    Presuming that we have Table1(id int, val text)

    create procedure getTruncatedText
    @givenId int
    declare @pointer varbinary(16)
    select @pointer = textptr (val) from Table1 where id = @givenId
    readtext Table1.val @pointer 1 32768 using chars

    The above will display the first 32768 characters from a text datatype.

    As for actually storing or directly using the value (readtext outputs) inside your sql code I'm not sure that's possible.


  3. #3
    Join Date
    Sep 2012


    Sorin, thank you for responding. Ultimately I used CONVERT to convert the first 16768 (our local max for converting TEXT to VARCHAR) characters to a VARCHAR and then passed that through an ETL to ORACLE where I converted it to a CLOB. Why a CLOB after I already had it in a VARCHAR? Well ORACLE VARCHAR2 maxes out at 4000 characters, and I already had code, that used the ORACLE DBMS_LOB functions for cutting a CLOB apart. I was assured by our customer that the string I was looking for was in the first 16768 characters so it was enough. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts