| Author |
 Topic  |
|
|
vinay.a
Starting Member
India
20 Posts |
 Posted - 04/25/2008 : 02:28:58
|
hi,
I am writing a SQL function in sql server 2005 to convert decimal number to hexadecimal number.
My code looks as follows:
-------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[toHex](@decValIn int)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @hexVal varchar(50)
DECLARE @decVal int
WHILE @decVal >16
BEGIN
SET @hexVal = @hexVal + CHAR(@decVal % 16)
SET @decVal = @decVal / 16
END
-- Return the result of the function
RETURN (@hexVal)
END
-------------------------------------------------
When I paass a query like the one below I get a 'NULL' as a result.
-------------------------------------------------
Select dbo.toHex(Bookid) as hexDecNum from table1 where Bookid=1991
-------------------------------------------------
Need some help with it.
regards,
vinay |
|
|
vinay.a
Starting Member
India
20 Posts |
Posted - 04/25/2008 : 02:35:42
|
The above code is missing some data:
While I return the data I do a REVERSE function on the @hexVal:
REVERSE(@hexVal)
I forgot to assign the input value '@decValIn' to ' @decVal' in the above code.
After doing the above changes, I stiil get the same 'NULL'
regards,
vinay |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
27659 Posts |
Posted - 04/25/2008 : 02:55:41
|
try like this:-
DECLARE @no int,@Ret varchar(100)
SET @no=1991--your decimal number
WHILE @no >0
BEGIN
SELECT @Ret= CASE WHEN @no % 16 <10 THEN CAST(@no % 16 AS varchar(2))
WHEN @no % 16 =10 THEN 'A'
WHEN @no % 16 =11 THEN 'B'
WHEN @no % 16 =12 THEN 'C'
WHEN @no % 16 =13 THEN 'D'
WHEN @no % 16 =14 THEN 'E'
WHEN @no % 16 =15 THEN 'F'
END + COALESCE(@Ret,'') ,
@no=@no /16
END
SELECT @Ret as 'Hex'---hexa equivalent
integrate this code in your function to get result |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 04/25/2008 : 03:16:22
|
DECLARE @Dec INT
SET @Dec = 65534
SELECT REPLACE(LTRIM(REPLACE(STUFF(master.dbo.fn_varbintohexstr(@Dec), 1, 2, ''), '0', ' ')), ' ', '0')-- 2000
If you are writing for SQL Server 2005, why are you posting in a SQL Server 2000 forum?
DECLARE @Dec INT
SET @Dec = 65534
SELECT REPLACE(LTRIM(REPLACE(STUFF(master.sys.fn_varbintohexstr(@Dec), 1, 2, ''), '0', ' ')), ' ', '0')-- 2005
E 12°55'05.25"
N 56°04'39.16" |
Edited by - Peso on 04/25/2008 03:17:51 |
|
|
|
vinay.a
Starting Member
India
20 Posts |
Posted - 04/25/2008 : 03:25:43
|
Thanks boss,
things working fine,
the below code gets me the hex no. But it's a little lengthy. Can u condense it please.
-------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[toHex](@decValIn int)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @hexVal varchar(50)
DECLARE @decVal int
DECLARE @remainder int
DECLARE @remainderChar char
SET @decVal = @decValIn
------------------------------------
--first loop of the while is extracted out
--Bacause the @remainderChar variable was not set to some value
--With out the below piece of code the result is 'NULL'
SET @remainder = @decVal % 16
if @remainder >9
BEGIN
SET @remainderChar = CASE @remainder
WHEN 10 THEN 'a'
WHEN 11 THEN 'b'
WHEN 12 THEN 'c'
WHEN 13 THEN 'd'
WHEN 14 THEN 'e'
WHEN 15 THEN 'f'
END
END
ELSE
BEGIN
SET @remainderChar = CAST(@remainder as char)
END
SET @hexVal = @remainderChar
SET @decVal = @decVal / 16
-----------------------------
WHILE @decVal >16
BEGIN
SET @remainder = @decVal % 16
if @remainder >9
BEGIN
SET @remainderChar = CASE @remainder
WHEN 10 THEN 'a'
WHEN 11 THEN 'b'
WHEN 12 THEN 'c'
WHEN 13 THEN 'd'
WHEN 14 THEN 'e'
WHEN 15 THEN 'f'
END
END
ELSE
BEGIN
SET @remainderChar = CAST(@remainder as char)
END
SET @hexVal = @hexVal + @remainderChar
SET @decVal = @decVal / 16
END
-----------------------------
SET @hexVal = @hexVal + CAST(@decVal as char)
-- Return the result of the function
RETURN (REVERSE(@hexVal))
END
-------------------------------------------------------
regards,
vinay |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 04/25/2008 : 03:56:03
|
Scroll up a little and look at the suggestion posted 10 minutes earlier.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
27659 Posts |
Posted - 04/25/2008 : 03:59:23
|
quote:
Originally posted by vinay.a
Thanks boss,
things working fine,
the below code gets me the hex no. But it's a little lengthy. Can u condense it please.
-------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[toHex](@decValIn int)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @hexVal varchar(50)
DECLARE @decVal int
DECLARE @remainder int
DECLARE @remainderChar char
SET @decVal = @decValIn
------------------------------------
--first loop of the while is extracted out
--Bacause the @remainderChar variable was not set to some value
--With out the below piece of code the result is 'NULL'
SET @remainder = @decVal % 16
if @remainder >9
BEGIN
SET @remainderChar = CASE @remainder
WHEN 10 THEN 'a'
WHEN 11 THEN 'b'
WHEN 12 THEN 'c'
WHEN 13 THEN 'd'
WHEN 14 THEN 'e'
WHEN 15 THEN 'f'
END
END
ELSE
BEGIN
SET @remainderChar = CAST(@remainder as char)
END
SET @hexVal = @remainderChar
SET @decVal = @decVal / 16
-----------------------------
WHILE @decVal >16
BEGIN
SET @remainder = @decVal % 16
if @remainder >9
BEGIN
SET @remainderChar = CASE @remainder
WHEN 10 THEN 'a'
WHEN 11 THEN 'b'
WHEN 12 THEN 'c'
WHEN 13 THEN 'd'
WHEN 14 THEN 'e'
WHEN 15 THEN 'f'
END
END
ELSE
BEGIN
SET @remainderChar = CAST(@remainder as char)
END
SET @hexVal = @hexVal + @remainderChar
SET @decVal = @decVal / 16
END
-----------------------------
SET @hexVal = @hexVal + CAST(@decVal as char)
-- Return the result of the function
RETURN (REVERSE(@hexVal))
END
-------------------------------------------------------
regards,
vinay
Why are you repeating the loop? Did you see alst part of my post whether i'm concatenating the returned character each time. I dont think you need to repeat this. |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 04/25/2008 : 04:34:23
|
04/25/2008 : 03:16:22
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
vinay.a
Starting Member
India
20 Posts |
Posted - 04/25/2008 : 04:51:22
|
Thanks for the help,
It's working
And sorry for posting in SQl Server 2000.
regards,
vinay |
|
|
|
madhivanan
Premature Yak Congratulator
India
20644 Posts |
Posted - 04/25/2008 : 05:49:19
|
quote:
Originally posted by Peso
DECLARE @Dec INT
SET @Dec = 65534
SELECT REPLACE(LTRIM(REPLACE(STUFF(master.dbo.fn_varbintohexstr(@Dec), 1, 2, ''), '0', ' ')), ' ', '0')-- 2000
If you are writing for SQL Server 2005, why are you posting in a SQL Server 2000 forum?
DECLARE @Dec INT
SET @Dec = 65534
SELECT REPLACE(LTRIM(REPLACE(STUFF(master.sys.fn_varbintohexstr(@Dec), 1, 2, ''), '0', ' ')), ' ', '0')-- 2005
E 12°55'05.25"
N 56°04'39.16"
or
select right(master.dbo.fn_varbintohexstr(@Dec), charindex('0',reverse(master.dbo.fn_varbintohexstr(@Dec)))-1)
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Conver - DEecimal no to Hexadecimal no., SQL fun()
