January 7, 2009 at 2:09 pm
Let's say I have a software version 16.04.01
I set variables to get 1604 out of that value (see below)
(I have them set as seperate variables because I use the pieces in various stages)
I need to pretend that 1604 is the hex value and I need to get the decimal equivalent
here's what I have so far:
declare @sw1 VARCHAR(2)
set @sw1 = left('%_SOFTWARE%',2)
declare @sw2 VARCHAR(2)
set @sw2 = substring('%_SOFTWARE%',4,2)
declare @sw3 VARCHAR(4)
set @sw3 = @sw1+@sw2
cast(@sw3 as int) doesn't work to convert from hex to dec
but if @sw3 was somehow coming through as 0x1604 it would work fine.
So - how do I pass a variable that would be 0x1604? Or how do I get this to work? Am I using the wrong datatypes for my variables?
January 7, 2009 at 2:43 pm
Hmmm... your code here works just fine...or maybe I'm not understand you request here!
Anyway try with NVARCHAR data type in your table to store the version number in yourtable then do all things as you are doing but declaring the nvarchar datatype!
January 7, 2009 at 2:47 pm
The value I'm expecting with the cast statement should be '5636' (converting 1604 from hex to dec)
But it keeps inserting '1604' into the table. I can't get it to give me 5636 unless I pass it 0x1604.
January 7, 2009 at 3:38 pm
n_parker (1/7/2009)
The value I'm expecting with the cast statement should be '5636' (converting 1604 from hex to dec)But it keeps inserting '1604' into the table. I can't get it to give me 5636 unless I pass it 0x1604.
SELECT CAST(0x1604 AS INT)
Ok! Try this:
create function hex2int(@s varchar(16)) --Convert hex to
returns bigint -- e.g. select dbo.hex2int('7ff2a5')
as begin
select @S=upper(@s)
declare @i int, @len int, @C char(1), @result bigint
select @len=len(@s), @i=@len, @result=case when @len>0 then 0 end
while (@i>0)
begin
select @C=substring(@s,@i,1), @result=@result
+(ASCII(@c)
-(case when @C between 'A' and 'F' then 55
else case when @C between '0' and '9' then 48 end
end))
*power(16.,@len-@i)
,@i=@i-1
end -- while
return @result
end -- function
TEST:
SELECT dbo.hex2int(1604)
January 7, 2009 at 3:43 pm
After you create the funstion you will see your results for the example that you give above:
declare @sw1 VARCHAR(2)
set @sw1 = left('16.04.01',2)
declare @sw2 VARCHAR(2)
set @sw2 = substring('16.04.01',4,2)
declare @sw3 VARCHAR(4)
set @sw3 = @sw1+@sw2
SELECT dbo.hex2int(@sw3)
January 7, 2009 at 7:31 pm
hmmm....not working. I feel so clueless.
I'm trying to call this SQL from an external source (Visual Build) and here is the message I get:
Msg 156, Level 15, State 1, Server MYSERVER, Procedure hex2int, Line 20
Incorrect syntax near the keyword 'declare'.
Process completed with exit code 1
Here's what I have in my window:
create function hex2int(@s varchar(16)) --Convert hex to
returns bigint -- e.g. select dbo.hex2int('7ff2a5')
as begin
select @S=upper(@s)
declare @i int, @len int, @C char(1), @result bigint
select @len=len(@s), @i=@len, @result=case when @len>0 then 0 end
while (@i>0)
begin
select @C=substring(@s,@i,1), @result=@result
+(ASCII(@c)
-(case when @C between 'A' and 'F' then 55
else case when @C between '0' and '9' then 48 end
end))
*power(16.,@len-@i)
,@i=@i-1
end -- while
return @result
end -- function
declare @sw1 VARCHAR(2)
set @sw1 = left('%_SOFTWARE%',2)
declare @sw2 VARCHAR(2)
set @sw2 = substring('%_SOFTWARE%',4,2)
declare @sw3 VARCHAR(4)
set @sw3 = @sw1+@sw2
SELECT dbo.hex2int(@sw3)
(just trying to get the value in the result window of Visual Build)
January 8, 2009 at 12:46 am
Are you sure that the substring function retrieves correct what do you want also the left function!
%_SOFTWARE_% - is this the column of the table or just the value as I write above '16.04.01'!
January 8, 2009 at 9:24 am
If [font="Courier New"]CAST(0x1604 AS int)[/font] returns what you want, this dynamic SQL will also return what you want.
I'm not that comfortable about the use of dynamic SQL here but the IF statement will avoid any trouble with the casting when the version string is not what you expect.
declare @sw1 VARCHAR(2)
set @sw1 = left('16.04.01',2)
declare @sw2 VARCHAR(2)
set @sw2 = substring('16.04.01',4,2)
declare @sw3 VARCHAR(4)
set @sw3 = @sw1+@sw2
IF (@sw3 LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F]')
EXEC('SELECT CAST(0x' + @sw3 + ' AS int)')
January 8, 2009 at 1:35 pm
The function above is very stable for converting from Hex to BigInt and I suggest to use the function, just the n_parker should know how to use it - also the others!
Yupeee!
:hehe:
January 9, 2009 at 3:55 am
Agree with you Dugi.
However, here is an improved version of the hex2int function that, from my testing, is almost twice as fast as yours. I think the main reasons for this are that (1) it avoids using the POWER function within the WHILE loop, and (2) avoids using floating point arithmetic.
CREATE FUNCTION hex2int(@s varchar(16))
RETURNS bigint
AS BEGIN
SELECT @S = UPPER(RTRIM(LTRIM(@s)))
DECLARE @n int, @cc int, @factor bigint, @acc bigint
SELECT @n = LEN(@s), @factor = 1, @acc = CASE WHEN @n > 0 THEN 0 END
WHILE (@n > 0) BEGIN
SELECT
@cc = ASCII(SUBSTRING(@s, @n, 1)),
@acc = @acc + @factor * (@cc - CASE WHEN (@cc BETWEEN 48 AND 57) THEN 48 WHEN (@cc BETWEEN 65 AND 70) THEN 55 ELSE NULL END),
@factor = @factor * 16,
@n = @n - 1
END -- WHILE
RETURN @acc
END -- FUNCTION
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply