Share via

How to Convert a SQL Server Text Column data to VarBinary/Image Column data and back.

  • Article

This article is just to show some code to help convert  data between SQL server text column and varbinary/image column.
**This also can help you using the SQL Server READTEXT, WRITETEXT and UPDATETEXT statements.
These kind of statements use pointer that are not trivial to SQL Server programmers.
PS: A image column and varbinary column can be equaly used.

The code below is commented and self-explained.

-- Creating one table with a text column.
--drop table #a
create table #a (id int identity, content_text text)
insert into #a values('ABCDEFGHIJ')
insert into #a values(REVERSE('ABCDEFGHIJ'))**

select * from #a

--drop table #c
create table #c (id int identity, content_image image)
insert into #c values (0x0) -- this byte will be deleted but its necessary to have a pointer for UpdateText statement
insert into #c values (0x0) -- this byte will be deleted but its necessary to have a pointer for UpdateText statement

select * from #c

-- this while copies data between source table (text) and destination (varbinary)
declare @max int
declare @id int 

select @max = MAX(id) from #a

 

while @max > 0

begin

declare @ptrsrc binary(16)
declare @ptrdest binary(16)
declare @qty_bytes int

-- these statements are just getting POINTERS to data, NOT DATA in fact. 
select @ptrsrc = TEXTPTR(conteudo_text) from #a where id = @max
select @ptrdest = TEXTPTR(conteudo_image) from #c where id = @max

select @qty_bytes = datalength(conteudo_text) from #a where id = @max
 
-- insert starting at 0 index, excluding 1 byte of data (initialization data) and copying all the src data.
UPDATETEXT #c.content_image @ptrdest 0 1 #a.content_text @ptrsrc

set @max = @max - 1

end

--> OK,NOW YOU HAVE YOUR DATA IN VARBINARY/IMAGE FORMAT.
-- verifying data
select * from #a
select * from #c

-- cleaning table "#a" to put data back in text format.
update #a
set conteudo_text = NULL

select * from #a

-- this while will copy data back to the table "#a", converting data again from image/varbinary to text.
declare @max int
declare @id int

select @max = MAX(id) from #c

while @max > 0

begin

declare @ptrdest binary(16)
declare @varbinary varbinary(max)

-- this statement is getting DATA not a pointer.
select @varbinary = content_image from #c where id = @max

-- this statement is getting just a POINTER again.
select @ptrdest = TEXTPTR(content_text) from #a where id = @max

-- this statement just put data inside pointed column.
WRITETEXT #a.content_text @ptrdest @varbinary

set @max = @max - 1

end

-- verifying data on the table
select * from #a