Conver - DEecimal no to Hexadecimal no., SQL fun()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conver - DEecimal no to Hexadecimal no., SQL fun()

Author

Topic

vinay.a
Starting Member

20 Posts

Posted - 2008-04-25 : 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

20 Posts

Posted - 2008-04-25 : 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

52326 Posts

Posted - 2008-04-25 : 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 03:16:22

[code]DECLARE @Dec INT

SET @Dec = 65534

SELECT REPLACE(LTRIM(REPLACE(STUFF(master.dbo.fn_varbintohexstr(@Dec), 1, 2, ''), '0', ' ')), ' ', '0')-- 2000[/code]

If you are writing for SQL Server 2005, why are you posting in a SQL Server 2000 forum?
[code]DECLARE @Dec INT

SET @Dec = 65534

SELECT REPLACE(LTRIM(REPLACE(STUFF(master.sys.fn_varbintohexstr(@Dec), 1, 2, ''), '0', ' ')), ' ', '0')-- 2005[/code]

E 12°55'05.25"
N 56°04'39.16"

vinay.a
Starting Member

20 Posts

Posted - 2008-04-25 : 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 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

52326 Posts

Posted - 2008-04-25 : 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 04:34:23

04/25/2008 : 03:16:22

E 12°55'05.25"
N 56°04'39.16"

vinay.a
Starting Member

20 Posts

Posted - 2008-04-25 : 04:51:22

Thanks for the help,

It's working

And sorry for posting in SQl Server 2000.

regards,
vinay

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-25 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources