Sunday, February 19, 2012

Convert Image Datatype To Int

Hi,
I have a table which has a field defined as Image. I guess it was defined
that way to hold any value or type.
From what I am reading it seems that I can cast from image to binary and
then from binary to varchar or int...
Whenever I try to convert I keep getting zero as a value. I set up the test
so that I should get an integer value of 5050, but it just does not seem to
work. Is there something wrong with my code:
declare @.c varchar(100)
declare @.trimmed_c varchar(100)
declare @.i int
select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
select @.i = cast(@.trimmed_c as int)
select @.i as 'deal_id'
Any help would be great, thanks.Depending on how the data was inserted into the image column. Here is an
example that shows successful conversion both ways.
create table tmp(img image default '0x0')
insert tmp values(default)
go
declare @.i int, @.b varbinary(4), @.ptr binary(16)
set @.i=123
set @.b=convert(binary(4),@.i)
select @.ptr=textptr(img)
from tmp
writetext tmp.img @.ptr @.b
go
select img,convert(int,convert(binary(4),img)) [i]
from tmp
go
drop table tmp
-oj
"Dianna" <Dianna@.discussions.microsoft.com> wrote in message
news:F9DA6D91-5256-495A-A7AB-700F955483BD@.microsoft.com...
> Hi,
> I have a table which has a field defined as Image. I guess it was defined
> that way to hold any value or type.
> From what I am reading it seems that I can cast from image to binary and
> then from binary to varchar or int...
> Whenever I try to convert I keep getting zero as a value. I set up the
> test
> so that I should get an integer value of 5050, but it just does not seem
> to
> work. Is there something wrong with my code:
> declare @.c varchar(100)
> declare @.trimmed_c varchar(100)
> declare @.i int
> select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
> from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
> select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
> select @.i = cast(@.trimmed_c as int)
> select @.i as 'deal_id'
> Any help would be great, thanks.
>

No comments:

Post a Comment