CODEWITHSUNDEEP
sqlsybasesap-ase
Mukesh Kumar
Mukesh Kumar

Reputation: 985

Replace string in UNITEXT column sybase database

We are using Sybase database and have the below table.

CREATE TABLE table_1
(id            VARCHAR(50) NOT NULL
,data_values   UNITEXT         NULL
,last_modified DATETIME        NULL)

I have requirement to replace the word stored the data_values column which is of UNITEXT type.

I found link but this is not helping how we can replace the words.

Query that I tried is below.

declare @val varbinary(16)
select  @val = textptr(data_values) from table_1
writetext table_1.data @val "Some text"

I have to dynamically generate the text to be set in data_values field. Text itself will be fetched from same column itself and have to processed(few words changes) before setting to the field.

Upvotes: 0

Views: 1987

Answers (1)

markp-fuso
markp-fuso

Reputation: 34174

Some background:

  • if you're running ASE 15.7 (or higher), you can declare a variable of type (uni)text
  • the textptr() function returns a varbinary(16) value which is actually a pointer to where the text data resides in the database [in the manuals they typically show @val = textptr(...) which is a bit misleading because textptr() isn't returning the value of the text column but rather a pointer to the text column]
  • an actual text column value can be accessed directly just like you would any other column
  • the writetext command provides an alternative to the update command when it comes to writing a value into a text column; update is a logged operation while writetext is, by default, a non-logged operation

Some example SQL (using your table definition):

insert table_1 values ('1','original.old text value',getdate())
go

declare @ptr     varbinary(16),
        @unitext unitext,
        @varchar varchar(16384)

-- obtain text pointer and unitext value for a desired row

select  @ptr     = textptr(data_values),                  -- pointer to unitext column
        @unitext = data_values,                           -- actual unitext value
        @varchar = convert(varchar(16384),data_values)    -- unitext converted to character
from    table_1
where   id = '1'

select 'original' as 'when', @unitext as '@unitext', @varchar as '@varchar'

-- logged update of the data_values column

begin tran

update  table_1
set     data_values = 'update.new text value'
where   id = '1'

select  @unitext = data_values,
        @varchar = convert(varchar(16384),data_values)
from    table_1
where   id = '1'

select 'after update' as 'when', @unitext as '@unitext', @varchar as '@varchar'

-- non-logged update of the data_values column

writetext table_1.data_values @ptr 'writetext.new text value'

select  @unitext = data_values,
        @varchar = convert(varchar(16384),data_values)
from    table_1
where   id = '1'

select 'after writetext' as 'when', @unitext as '@unitext', @varchar as '@varchar'

-- rollback to original value

rollback tran

select  @unitext = data_values,
        @varchar = convert(varchar(16384),data_values)
from    table_1
where   id = '1'

select 'after rollback' as 'when', @unitext as '@unitext', @varchar as '@varchar'
go

 when             @unitext                      @varchar
 -----------------------------------------------------------------------
 original         original.old text value       original.old text value

 when             @unitext                      @varchar
 -----------------------------------------------------------------------
 after update     update.new text value         update.new text value

 when             @unitext                      @varchar
 -----------------------------------------------------------------------
 after writetext  writetext.new text value      writetext.new text value

 when             @unitext                      @varchar
 -----------------------------------------------------------------------
 after rollback   original.old text value       original.old text value

Edited:

  • updated to pull the value into both a unitext variable and a varchar variable
  • there are still some string-related functions that do not work with (uni)text so it may be necessary to work with the character version of the data
  • also, if varchar happens to corrupt the data then it's possible to replace varchar with univarchar though the max length will need to be reduced from 16384 to 8192

Upvotes: 1

Related Questions