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?
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
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.
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.