DEC TO HEX CONVERSION WHILE IMPORTING

I HAVE DECIMAL NUMBER IN MY TEST TABLE COLUMN CARD_NUMBER
AS FOLLOWS
2571549730.0
2571549826.0
2571550034.0
2571550210.0
2571550306.0
2571551378.0
2571551586.0
2571551682.0
2571551762.0
2571551874.0
I WANT TO CREATE A TEMP TABLE AND MOVE THIS COLUMN BUT
BEFORE MOVING IT SHOULD CONVERT INTO HEXADECIMAL NUMBER
WHILE INSERTING INTO TEMP TABLE
THANKS
Mustafa,
If your decimal values fit into a bigint, you could write a
user-defined function (SQL Server 2000 required):
create function dec2hex (
@.decimal bigint
) returns varbinary(20) as begin
declare @.b varbinary(20)
set @.b = 0x
while @.decimal > 0 begin
set @.b = cast(cast(@.decimal%256 as tinyint) as binary(1)) + @.b
set @.decimal = @.decimal/256
end
return @.b
end
go
select dbo.dec2hex(1)
select dbo.dec2hex(2571551874.0)
go
drop function dec2hex
-- Steve Kass
-- Drew University
-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
MUSTAFA wrote:

>I HAVE DECIMAL NUMBER IN MY TEST TABLE COLUMN CARD_NUMBER
>AS FOLLOWS
>2571549730.0
>2571549826.0
>2571550034.0
>2571550210.0
>2571550306.0
>2571551378.0
>2571551586.0
>2571551682.0
>2571551762.0
>2571551874.0
>I WANT TO CREATE A TEMP TABLE AND MOVE THIS COLUMN BUT
>BEFORE MOVING IT SHOULD CONVERT INTO HEXADECIMAL NUMBER
>WHILE INSERTING INTO TEMP TABLE
>
>THANKS
>
|||YES IT WORKS
THANKS Mr. STEVE

>--Original Message--
>Mustafa,
> If your decimal values fit into a bigint, you could
write a
>user-defined function (SQL Server 2000 required):
>create function dec2hex (
> @.decimal bigint
>) returns varbinary(20) as begin
> declare @.b varbinary(20)
> set @.b = 0x
> while @.decimal > 0 begin
> set @.b = cast(cast(@.decimal%256 as tinyint) as binary
(1)) + @.b[vbcol=seagreen]
> set @.decimal = @.decimal/256
> end
> return @.b
>end
>go
>select dbo.dec2hex(1)
>select dbo.dec2hex(2571551874.0)
>go
>drop function dec2hex
>-- Steve Kass
>-- Drew University
>-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
>MUSTAFA wrote:
CARD_NUMBER
>.
>
|||Dear Mr. Steve
i have created user define function dec2hex in sql server
2000 and when i access it using sql server query analyser
it give me desired result i.e it convert decimal into
hexadecimal number
The query is as follow
select dbo.dec2hex(card_number) as card_number from
tbltest
But when i used it using VB6 recordset object it show ?
rather then showing hexadecimal number
thanks

>--Original Message--
>Mustafa,
> If your decimal values fit into a bigint, you could
write a
>user-defined function (SQL Server 2000 required):
>create function dec2hex (
> @.decimal bigint
>) returns varbinary(20) as begin
> declare @.b varbinary(20)
> set @.b = 0x
> while @.decimal > 0 begin
> set @.b = cast(cast(@.decimal%256 as tinyint) as binary
(1)) + @.b[vbcol=seagreen]
> set @.decimal = @.decimal/256
> end
> return @.b
>end
>go
>select dbo.dec2hex(1)
>select dbo.dec2hex(2571551874.0)
>go
>drop function dec2hex
>-- Steve Kass
>-- Drew University
>-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
>MUSTAFA wrote:
CARD_NUMBER
>.
>
|||Mustafa,
This function returns a varbinary(20) value, so if you are seeing ?
somewhere, then something in your application is not displaying
varbinary values correctly. I don't know how VB6 recordsets display
information of varbinary type. Can your VB recordset object display
what is in this recordset?
select 0x123456AB as aBinaryValue
It sounds like a VB issue, not a SQL Server issue.
Steve Kass
Drew University
mustafa wrote:
[vbcol=seagreen]
>Dear Mr. Steve
>i have created user define function dec2hex in sql server
>2000 and when i access it using sql server query analyser
>it give me desired result i.e it convert decimal into
>hexadecimal number
>The query is as follow
>select dbo.dec2hex(card_number) as card_number from
>tbltest
>But when i used it using VB6 recordset object it show ?
>rather then showing hexadecimal number
>thanks
>
>
>write a
>
>(1)) + @.b
>
>CARD_NUMBER
>