Converting a number to hexadcimal

Is there any function in SQL-Server which converts a number into its Hexadecimal equivalent..? if not, how should I convert a number to its hexadecimal equivalent..?
Thanks
Jakesomething like:
SELECT CAST( 123456 AS BINARY(4) )|||Originally posted by harshal_in
something like:
SELECT CAST( 123456 AS BINARY(4) )

Thanks Harsh

Converting a HEX string to its binary representation

I am trying to take a hexadecimal representation of a binary number and convert to the true binary representation so that I can compare it against a binary field in one of my tables.

After reading the documentation it seems I should be able to do this with the CAST or CONVERT function. However it does not appear to be working correctly.

Can you tell me why this T-SQL code produces the wrong binary value:

DECLARE @.value binary(16)

SELECT @.value = CONVERT(binary, '0764DE49749F274EB924E1552FFE09EC')

PRINT @.value

This prints out: 0x30373634444534393734394632373445

That is not correct it should be: 0x0764DE49749F274EB924E1552FFE09EC

Thanks

Chris:

If you are really only dealing with conversion of a constant what you want to do instead of

SELECT @.value = CONVERT(binary, '0764DE49749F274EB924E1552FFE09EC')

is

SELECT @.value = CONVERT(binary, 0x0764DE49749F274EB924E1552FFE09EC)

Dave

|||

You might also be able to use directly:

SELECT @.value = 0x0764DE49749F274EB924E1552FFE09EC

|||

Thanks but my example is just that an example to demonstrate the problem.

The real issues is that I am recieving the binary values as hexadecimal through xml. I need to convert these to their binary representations so that I can query for them. Here is a more complete example:

ALTER PROCEDURE dbo.spc_rels_byRelIds
(
@.ItemsXML TEXT
)
AS

DECLARE @.hDoc int

EXEC sp_xml_preparedocument @.hDoc output, @.ItemsXML

SELECT *
FROM tblRelAttrVals
INNER JOIN
(
SELECT
CONVERT(binary, relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)
) XmlItems
ON grel = relID

EXEC sp_xml_removedocument @.hDoc

Where grel is binary(16) column in the tblRelAttrVals table.

|||

Chris:

Look at this example from earlier this year:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386406&SiteID=1

|||

Ok so I have this as an example of a stored proc:

if @.hexStr like '%[^abcdef0123456789]%'
BEGIN
set @.b = NULL
return @.b
END

DECLARE @.sql nvarchar(100)
DECLARE @.parms nvarchar(100)

SET @.sql = N'select @.out=0x' + @.hexStr
SET @.parms = N'@.out varbinary(30) output'
EXEC sp_executesql @.sql, @.parms, @.out = @.b output

The problem is I really need a function so that I can do this:

SELECT
dbo.HexToBin(relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

But functions won't let you call exec sp_executesql from within them.

Does anybody see a way around this?

|||

You didn't mention the version of SQL Server you are using. Below are few optimized solutions that doesn't require dynamic SQL. Use the scalar UDF solution only if you have less number of rows in the table and performance is not important for you. Otherwise, the scalar UDF approach will perform poorly than say inlining the expression in the UDF directly in the SELECT statement.

-- Scalar UDF for SQL Server 2000:

create function hexstr2bin4 (@.hexstr char(8))
returns bigint
with schemabinding
as
begin
return (
((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 7))
+ ((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 6))
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 5))
+ ((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 4))
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 3))
+ ((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 2))
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 1))
+ ((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 0))
)
end
go

-- Use it like below:

SELECT
dbo.hexstr2bin4(relID) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

-- Inline TVF which provides the best performance for using in SELECT statements

-- But this will work only in SQL Server 2005.

create function hexstr2bin4 (@.hexstr char(8))
returns table
as
return (
select ((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 7))
+ ((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 6))
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 5))
+ ((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 4))
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 3))
+ ((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 2))
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 1))
+ ((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1) * power(cast(16 as bigint), 0)) as bin4
)

go

-- Use it like below:

SELECT
h.bin4 as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

CROSS APPLY dbo.hexstr2bin4(relID) as h

-- or

SELECT
(SELECT h.bin4 FROM dbo.hexstr2bin4(relID) as h) as relbID
FROM OPENXML (@.hDoc, 'Root/items', 1)
WITH
(
relID varchar(32)
)

|||Thank you very much. I have to support SQL 2000 and SQL 2005. How is the performance for a 16 byte binary value? The SQL 2000 method seems a bit heavy handed, especially being run within a select statement.|||

The performance of the scalar UDF depends on the number of rows on which you are running the SELECT statement to perform the conversion. You need to compare the scalar UDF against the inline approach for your dataset / queries and see. For SQL Server 2005, you can use the expression below in an inline TVF to get the best performance.

Also, below is another way to convert the string to binary. You can extend it to support any arbitrary length upto 8000 bytes.

create function hexstr2bin (@.hexstr varchar(32))

returns binary(32)

as

begin

return

cast((charindex(lower(substring(@.hexstr, 2, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 1, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 4, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 3, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 6, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 5, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 8, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 7, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 10, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 9, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 12, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 11, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 14, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 13, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 16, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 15, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 18, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 17, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 20, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 19, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 22, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 21, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 24, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 23, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 26, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 25, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 28, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 27, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 30, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 29, 1)), '0123456789abcdef')-1)*16) as binary(1)) +
cast((charindex(lower(substring(@.hexstr, 32, 1)), '0123456789abcdef')-1)
+ ((charindex(lower(substring(@.hexstr, 31, 1)), '0123456789abcdef')-1)*16) as binary(1))
end

Converting a binary/hexadecimal to Varchar

How should I convert a binary/hexadecimal value to Varchar
Thanks in Advance,
JakeOriginally posted by Jake K
How should I convert a binary/hexadecimal value to Varchar

Thanks in Advance,
Jake

select cast(Binaryvalue as varchar)sqlsql

Convert string to hex to int

My table has some hexadecimal data but stored as string. I want to
convert this to hex and then to int. How do I do this?
Data in my table:
139D5
6374
63B2
620B
ABC7
6391
6FA6
604A
139D6
This should be ideally
0x139D5
0x6374
0x63B2
0x620B
0xABC7
0x6391
0x6FA6
0x604A
0x139D6
How do I convert 139D5 to 0x139D5 so that I can cast it as an integer?
declare @.a varchar (1000)
set @.a='139D5'
select cast(cast (@.a as varbinary) as int)
select cast (0x 139D5 as int)
These two stmts give different results. Only the second one is correct.
Please help.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Why did you save hexadecimal value to string? Instead you can directly
store it as integer
Madhivanan|||Hi
Check out this previous post:
http://tinyurl.com/6syea
John
"male hit" wrote:

> My table has some hexadecimal data but stored as string. I want to
> convert this to hex and then to int. How do I do this?
> Data in my table:
> 139D5
> 6374
> 63B2
> 620B
> ABC7
> 6391
> 6FA6
> 604A
> 139D6
> This should be ideally
> 0x139D5
> 0x6374
> 0x63B2
> 0x620B
> 0xABC7
> 0x6391
> 0x6FA6
> 0x604A
> 0x139D6
> How do I convert 139D5 to 0x139D5 so that I can cast it as an integer?
> declare @.a varchar (1000)
> set @.a='139D5'
> select cast(cast (@.a as varbinary) as int)
> select cast (0x 139D5 as int)
> These two stmts give different results. Only the second one is correct.
> Please help.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

Convert or cast HexaDecimal to Bigint

Hi ,

I have a hexadecimal string value. I want to convert it to Bigint in Sql Server 2005.

The Hexadecimal value is '0x000000000000000F'.

How is it possible to convert into Bigint.

Please help me

Thanks in advance

Srinivas

SELECT CONVERT(bigint, 0x000000000000000F)|||

There are many ways to convert a string with hexadecimal value to integer value. Below is one technique using a table of numbers:

declare @.t varchar (30)
select @.t = '000000000000000F'
select sum(
case lower( substring( reverse(@.t), number , 1 ) )
when '0' then 0
when '1' then 1
when '2' then 2
when '3' then 3
when '4' then 4
when '5' then 5
when '6' then 6
when '7' then 7
when '8' then 8
when '9' then 9
when 'a' then 10
when 'b' then 11
when 'c' then 12
when 'd' then 13
when 'e' then 14
when 'f' then 15
when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
end * power( cast(16 as bigint), number - 1 )
)
from Numbers n
where number between 1 and len( @.t )
go

Note that it doesn't handle conversions into negative bigint values but that can be done easily.

|||

Hi ,

I think directly using Cast or Convert will not work.

The Bigint value corresponding to Hexadecimal value ('0x000000000000000F') is 1000000002.

I require a function which would take Hexadecimal value as input parameters and return me a big int value.

Thanks for the help.

Srinivas Govada

|||Then create a user-defined function with the code given by Umachandar and pass the hex string to that function and get the calculated bigint value as the return value from the function.|||

Hi

What does number refer to and what is there in table Numbers.

Please provide structure of table numbers and records in table.

Regards,

Srinivas Govada

convert images to hexadecimal

Hello guys,

Have any one tried to convert an image to a hexadecimal string, and saving it to sql server?

Thanks..

You might find base64 strings a bit more compact. Try this for starters. It only takes TWO lines to do the work!

<%@.PageLanguage="C#" %>

<%@.ImportNamespace="System.IO" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

protectedvoid LinkButton1_Click(object sender,EventArgs e)

{

using (StreamReader sr =newStreamReader(MapPath("TestPicture.JPG")))

{

BinaryReader br =newBinaryReader(sr.BaseStream);

byte[] data = br.ReadBytes((int)br.BaseStream.Length);

string dataToSave =Convert.ToBase64String(data);

// show it

TextBox1.Text = dataToSave;

}

}

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:LinkButtonID="LinkButton1"runat="server"OnClick="LinkButton1_Click">GetPictureAsText</asp:LinkButton></div>

<asp:TextBoxID="TextBox1"runat="server"TextMode="MultiLine"Width="100%"Height="24em"></asp:TextBox>

</form>

</body>

</html>

|||

Thanks for the code. It worked fine. But I want to convert it to hexadecimal string. Its a requirement asked by the client...

Thanks for the help

|||

Try this

TextBox1.Text =BitConverter.ToString(data).Replace("-","");

Convert image to hexadecimal

I want to export data from my tables by generating insert statements,
including data of type image. To avoid having to use textcopy.exe or
textptr, I want to have the image data part of the insert-statements by
converting the binary image data to hexadecimal strings. Also, the
image data is larger then 8000 so a simple convert won't work. How do I
do the conversion from image to hex?As part of the DB Ghost evaluation there is a free scipter component for
scripting databases including data into insert statements. It handles image
and binary data by converting to hexidecimal and has a COM interface which
you can use and distribute freely.
http://www.dbghost.com
"Jacques Roumimper" wrote:

> I want to export data from my tables by generating insert statements,
> including data of type image. To avoid having to use textcopy.exe or
> textptr, I want to have the image data part of the insert-statements by
> converting the binary image data to hexadecimal strings. Also, the
> image data is larger then 8000 so a simple convert won't work. How do I
> do the conversion from image to hex?
>|||If you can use c# do this:
public static string BinToString(Byte[] binValue)
{
char[] hexCode =
{'0','1','2','3','4','5','6','7','8','9'
,'A','B','C','D','E','F'};
StringBuilder sb = new StringBuilder();
foreach (byte b in binValue)
{
sb.Append(Convert.ToString(hexCode[b >> 4]));
sb.Append(Convert.ToString(hexCode[b & 0xF]));
}
return "0x" + sb.ToString();
}
I am not 100% sure if web data administrator
http://www.microsoft.com/downloads/...&displaylang=en
can script image fields. Worth a try!
Mathias|||I need to do this in SQL, what would be the Transact SQL equivalent of
your C# code?

Convert hexadecimal value to real data type

Hi ,

I want to convert hexadecimal to numeric data type. Using directly Cast or Convert function is not working.

Please suggest an alternative how to retrieve the numeric value of binary data .

Thanks in advance

Regards

Srinivas Govada

Srinivas,

Please show us what does not work for you. The following is

fine:

declare @.n numeric(10,2)

set @.n = 1.23

select cast(@.n as varbinary(20))

-- returns 0x0A0200017B000000

select cast(0x0A0200017B000000 as numeric(10,2))

If you are trying to convert binary representations of

[float] values to [float], you can use this code:

declare @.b binary(8)

set @.b = 0xC094D954FB549F95

declare @.s bit

declare @.e smallint

declare @.m float

set @.s = case when substring(@.b,1,1) >= 0x80 then 1 else 0 end

set @.e = (substring(@.b,1,2)&32752)/16-1022

set @.m = cast(substring(@.b,6,3) as int)/2097152e0/536870912e0

+ (substring(@.b,2,4)&268435455)/536870912e0+0.5e0

select case when @.s = 1 then -1e0 else 1e0 end * @.m * power(2e0,@.e)

Steve Kass

Drew University

www.stevekass.com

Srinivas Govada@.discussions.microsoft.com wrote:

> Hi ,

>

> I want to convert hexadecimal to numeric data type. Using directly Cast

> or Convert function is not working.

>

> Please suggest an alternative how to retrieve the numeric value of

> binary data .

>

> Thanks in advance

>

> Regards

>

> Srinivas Govada

>

>

>

>

Convert hexadecimal to varchar

Hi all,
I need to convert a value in hexadecimal to varchar, and this does not
seem to be as easy as
DECLARE @.hex varbinary
DECLARE @.str varchar
SET @.str = 'test'
--PRINT @.str
SET @.hex = 0x0000000000000001FFFF07D600002710
SET @.str = CAST(@.hex as varchar)
Any ideas? Has anyone tried this before? The reason I need this in
varchar is because I'm going to have to run a stored procedure based on
the hexadecimal value in a loop so I run the sp against all my
hexadecimal values. Any help would be greatly appreciated. Thanks.
-StuThere's the "undocumented" function fn_varbintohexstr that will do the
trick. If you prefer to go with a "documented" method, you can try
something like the following:
CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.hex VARCHAR(400)
SELECT @.hex = '0x'
DECLARE @.i INT
SELECT @.i = 1
WHILE (@.i < DATALENGTH(@.val))
BEGIN
SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i, 1)
& 240) / 16 + 1, 1)
SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1)
& 15 + 1, 1)
SELECT @.i = @.i + 1
END
RETURN @.hex
END
GO
DECLARE @.hex VARBINARY(40)
SET @.hex = 0x0000000000000001FFFF07D600002710
SELECT dbo.udf_VarB2Hex (@.hex)
<stuart.karp@.gmail.com> wrote in message
news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
> Hi all,
> I need to convert a value in hexadecimal to varchar, and this does not
> seem to be as easy as
> DECLARE @.hex varbinary
> DECLARE @.str varchar
> SET @.str = 'test'
> --PRINT @.str
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SET @.str = CAST(@.hex as varchar)
> Any ideas? Has anyone tried this before? The reason I need this in
> varchar is because I'm going to have to run a stored procedure based on
> the hexadecimal value in a loop so I run the sp against all my
> hexadecimal values. Any help would be greatly appreciated. Thanks.
> -Stu
>|||Search SQL2000 BOL for string 'sp_hexadecimal'. You can create that stored
procedure and use it as follows (for instance):
declare @.s varbinary(20), @.out varchar(20)
select @.s = first
from sysindexes
where id = 4
select @.s
exec master..sp_hexadecimal @.s, @.out output
select @.out
Linchi
"Mike C#" wrote:
> There's the "undocumented" function fn_varbintohexstr that will do the
> trick. If you prefer to go with a "documented" method, you can try
> something like the following:
> CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.hex VARCHAR(400)
> SELECT @.hex = '0x'
> DECLARE @.i INT
> SELECT @.i = 1
> WHILE (@.i < DATALENGTH(@.val))
> BEGIN
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i, 1)
> & 240) / 16 + 1, 1)
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1)
> & 15 + 1, 1)
> SELECT @.i = @.i + 1
> END
> RETURN @.hex
> END
> GO
> DECLARE @.hex VARBINARY(40)
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SELECT dbo.udf_VarB2Hex (@.hex)
>
> <stuart.karp@.gmail.com> wrote in message
> news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
> > Hi all,
> >
> > I need to convert a value in hexadecimal to varchar, and this does not
> > seem to be as easy as
> > DECLARE @.hex varbinary
> > DECLARE @.str varchar
> > SET @.str = 'test'
> > --PRINT @.str
> > SET @.hex = 0x0000000000000001FFFF07D600002710
> >
> > SET @.str = CAST(@.hex as varchar)
> >
> > Any ideas? Has anyone tried this before? The reason I need this in
> > varchar is because I'm going to have to run a stored procedure based on
> > the hexadecimal value in a loop so I run the sp against all my
> > hexadecimal values. Any help would be greatly appreciated. Thanks.
> >
> > -Stu
> >
>
>|||Hi Mike C#,
Thanks for the example. However you'll notice that for my example it's
2 more than a regular hexadecimal number. For your example it will
remove the 10 at the end of 0x0000000000000001FFFF07D600002710
This number is a GUID, and I need to run my stored procedure against
this exact number, which I want to turn into a string. Is there a good
way of modifying your code for this longer version that I have. I
would think that I could also remove 2 zeros at the beginning of my
GUID, but that's probably not the best idea, since some of my guids use
the beginning of this, ie something like
0xED2CD6A2E653DA4191B3A67272795AE8.
Anymore help to this? Thanks.
Mike C# wrote:
> There's the "undocumented" function fn_varbintohexstr that will do the
> trick. If you prefer to go with a "documented" method, you can try
> something like the following:
> CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.hex VARCHAR(400)
> SELECT @.hex = '0x'
> DECLARE @.i INT
> SELECT @.i = 1
> WHILE (@.i < DATALENGTH(@.val))
> BEGIN
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i, 1)
> & 240) / 16 + 1, 1)
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1)
> & 15 + 1, 1)
> SELECT @.i = @.i + 1
> END
> RETURN @.hex
> END
> GO
> DECLARE @.hex VARBINARY(40)
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SELECT dbo.udf_VarB2Hex (@.hex)
>
> <stuart.karp@.gmail.com> wrote in message
> news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
> > Hi all,
> >
> > I need to convert a value in hexadecimal to varchar, and this does not
> > seem to be as easy as
> > DECLARE @.hex varbinary
> > DECLARE @.str varchar
> > SET @.str = 'test'
> > --PRINT @.str
> > SET @.hex = 0x0000000000000001FFFF07D600002710
> >
> > SET @.str = CAST(@.hex as varchar)
> >
> > Any ideas? Has anyone tried this before? The reason I need this in
> > varchar is because I'm going to have to run a stored procedure based on
> > the hexadecimal value in a loop so I run the sp against all my
> > hexadecimal values. Any help would be greatly appreciated. Thanks.
> >
> > -Stu
> >|||Thanks Linchi.
That worked perfectly.
Appreciate it muchly.
Linchi Shea wrote:
> Search SQL2000 BOL for string 'sp_hexadecimal'. You can create that stored
> procedure and use it as follows (for instance):
> declare @.s varbinary(20), @.out varchar(20)
> select @.s = first
> from sysindexes
> where id = 4
> select @.s
> exec master..sp_hexadecimal @.s, @.out output
> select @.out
> Linchi
> "Mike C#" wrote:
> > There's the "undocumented" function fn_varbintohexstr that will do the
> > trick. If you prefer to go with a "documented" method, you can try
> > something like the following:
> >
> > CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
> > RETURNS VARCHAR(400)
> > AS
> > BEGIN
> > DECLARE @.hex VARCHAR(400)
> > SELECT @.hex = '0x'
> > DECLARE @.i INT
> > SELECT @.i = 1
> > WHILE (@.i < DATALENGTH(@.val))
> > BEGIN
> > SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i, 1)
> > & 240) / 16 + 1, 1)
> > SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1)
> > & 15 + 1, 1)
> > SELECT @.i = @.i + 1
> > END
> > RETURN @.hex
> > END
> > GO
> > DECLARE @.hex VARBINARY(40)
> > SET @.hex = 0x0000000000000001FFFF07D600002710
> > SELECT dbo.udf_VarB2Hex (@.hex)
> >
> >
> > <stuart.karp@.gmail.com> wrote in message
> > news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
> > > Hi all,
> > >
> > > I need to convert a value in hexadecimal to varchar, and this does not
> > > seem to be as easy as
> > > DECLARE @.hex varbinary
> > > DECLARE @.str varchar
> > > SET @.str = 'test'
> > > --PRINT @.str
> > > SET @.hex = 0x0000000000000001FFFF07D600002710
> > >
> > > SET @.str = CAST(@.hex as varchar)
> > >
> > > Any ideas? Has anyone tried this before? The reason I need this in
> > > varchar is because I'm going to have to run a stored procedure based on
> > > the hexadecimal value in a loop so I run the sp against all my
> > > hexadecimal values. Any help would be greatly appreciated. Thanks.
> > >
> > > -Stu
> > >
> >
> >
> >|||Yeah, change the line
WHILE (@.i < DATALENGTH(@.val))
to
WHILE (@.i <= DATALENGTH(@.val))
<stuart.karp@.gmail.com> wrote in message
news:1156188225.257923.238440@.74g2000cwt.googlegroups.com...
> Hi Mike C#,
> Thanks for the example. However you'll notice that for my example it's
> 2 more than a regular hexadecimal number. For your example it will
> remove the 10 at the end of 0x0000000000000001FFFF07D600002710
> This number is a GUID, and I need to run my stored procedure against
> this exact number, which I want to turn into a string. Is there a good
> way of modifying your code for this longer version that I have. I
> would think that I could also remove 2 zeros at the beginning of my
> GUID, but that's probably not the best idea, since some of my guids use
> the beginning of this, ie something like
> 0xED2CD6A2E653DA4191B3A67272795AE8.
> Anymore help to this? Thanks.
> Mike C# wrote:
>> There's the "undocumented" function fn_varbintohexstr that will do the
>> trick. If you prefer to go with a "documented" method, you can try
>> something like the following:
>> CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
>> RETURNS VARCHAR(400)
>> AS
>> BEGIN
>> DECLARE @.hex VARCHAR(400)
>> SELECT @.hex = '0x'
>> DECLARE @.i INT
>> SELECT @.i = 1
>> WHILE (@.i < DATALENGTH(@.val))
>> BEGIN
>> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i,
>> 1)
>> & 240) / 16 + 1, 1)
>> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i,
>> 1)
>> & 15 + 1, 1)
>> SELECT @.i = @.i + 1
>> END
>> RETURN @.hex
>> END
>> GO
>> DECLARE @.hex VARBINARY(40)
>> SET @.hex = 0x0000000000000001FFFF07D600002710
>> SELECT dbo.udf_VarB2Hex (@.hex)
>>
>> <stuart.karp@.gmail.com> wrote in message
>> news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
>> > Hi all,
>> >
>> > I need to convert a value in hexadecimal to varchar, and this does not
>> > seem to be as easy as
>> > DECLARE @.hex varbinary
>> > DECLARE @.str varchar
>> > SET @.str = 'test'
>> > --PRINT @.str
>> > SET @.hex = 0x0000000000000001FFFF07D600002710
>> >
>> > SET @.str = CAST(@.hex as varchar)
>> >
>> > Any ideas? Has anyone tried this before? The reason I need this in
>> > varchar is because I'm going to have to run a stored procedure based on
>> > the hexadecimal value in a loop so I run the sp against all my
>> > hexadecimal values. Any help would be greatly appreciated. Thanks.
>> >
>> > -Stu
>> >
>

Convert hexadecimal to varchar

Hi all,
I need to convert a value in hexadecimal to varchar, and this does not
seem to be as easy as
DECLARE @.hex varbinary
DECLARE @.str varchar
SET @.str = 'test'
--PRINT @.str
SET @.hex = 0x0000000000000001FFFF07D600002710
SET @.str = CAST(@.hex as varchar)
Any ideas? Has anyone tried this before? The reason I need this in
varchar is because I'm going to have to run a stored procedure based on
the hexadecimal value in a loop so I run the sp against all my
hexadecimal values. Any help would be greatly appreciated. Thanks.
-StuThere's the "undocumented" function fn_varbintohexstr that will do the
trick. If you prefer to go with a "documented" method, you can try
something like the following:
CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.hex VARCHAR(400)
SELECT @.hex = '0x'
DECLARE @.i INT
SELECT @.i = 1
WHILE (@.i < DATALENGTH(@.val))
BEGIN
SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i, 1)
& 240) / 16 + 1, 1)
SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1)
& 15 + 1, 1)
SELECT @.i = @.i + 1
END
RETURN @.hex
END
GO
DECLARE @.hex VARBINARY(40)
SET @.hex = 0x0000000000000001FFFF07D600002710
SELECT dbo.udf_VarB2Hex (@.hex)
<stuart.karp@.gmail.com> wrote in message
news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
> Hi all,
> I need to convert a value in hexadecimal to varchar, and this does not
> seem to be as easy as
> DECLARE @.hex varbinary
> DECLARE @.str varchar
> SET @.str = 'test'
> --PRINT @.str
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SET @.str = CAST(@.hex as varchar)
> Any ideas? Has anyone tried this before? The reason I need this in
> varchar is because I'm going to have to run a stored procedure based on
> the hexadecimal value in a loop so I run the sp against all my
> hexadecimal values. Any help would be greatly appreciated. Thanks.
> -Stu
>|||Search SQL2000 BOL for string 'sp_hexadecimal'. You can create that stored
procedure and use it as follows (for instance):
declare @.s varbinary(20), @.out varchar(20)
select @.s = first
from sysindexes
where id = 4
select @.s
exec master..sp_hexadecimal @.s, @.out output
select @.out
Linchi
"Mike C#" wrote:

> There's the "undocumented" function fn_varbintohexstr that will do the
> trick. If you prefer to go with a "documented" method, you can try
> something like the following:
> CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.hex VARCHAR(400)
> SELECT @.hex = '0x'
> DECLARE @.i INT
> SELECT @.i = 1
> WHILE (@.i < DATALENGTH(@.val))
> BEGIN
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i,
1)
> & 240) / 16 + 1, 1)
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1
)
> & 15 + 1, 1)
> SELECT @.i = @.i + 1
> END
> RETURN @.hex
> END
> GO
> DECLARE @.hex VARBINARY(40)
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SELECT dbo.udf_VarB2Hex (@.hex)
>
> <stuart.karp@.gmail.com> wrote in message
> news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...
>
>|||Hi Mike C#,
Thanks for the example. However you'll notice that for my example it's
2 more than a regular hexadecimal number. For your example it will
remove the 10 at the end of 0x0000000000000001FFFF07D600002710
This number is a GUID, and I need to run my stored procedure against
this exact number, which I want to turn into a string. Is there a good
way of modifying your code for this longer version that I have. I
would think that I could also remove 2 zeros at the beginning of my
GUID, but that's probably not the best idea, since some of my guids use
the beginning of this, ie something like
0xED2CD6A2E653DA4191B3A67272795AE8.
Anymore help to this? Thanks.
Mike C# wrote:[vbcol=seagreen]
> There's the "undocumented" function fn_varbintohexstr that will do the
> trick. If you prefer to go with a "documented" method, you can try
> something like the following:
> CREATE FUNCTION dbo.udf_VarB2Hex(@.val VARBINARY(200))
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.hex VARCHAR(400)
> SELECT @.hex = '0x'
> DECLARE @.i INT
> SELECT @.i = 1
> WHILE (@.i < DATALENGTH(@.val))
> BEGIN
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', (SUBSTRING(@.val, @.i,
1)
> & 240) / 16 + 1, 1)
> SELECT @.hex = @.hex + SUBSTRING('0123456789abcdef', SUBSTRING(@.val, @.i, 1
)
> & 15 + 1, 1)
> SELECT @.i = @.i + 1
> END
> RETURN @.hex
> END
> GO
> DECLARE @.hex VARBINARY(40)
> SET @.hex = 0x0000000000000001FFFF07D600002710
> SELECT dbo.udf_VarB2Hex (@.hex)
>
> <stuart.karp@.gmail.com> wrote in message
> news:1156184550.176997.117550@.h48g2000cwc.googlegroups.com...|||Thanks Linchi.
That worked perfectly.
Appreciate it muchly.
Linchi Shea wrote:[vbcol=seagreen]
> Search SQL2000 BOL for string 'sp_hexadecimal'. You can create that stored
> procedure and use it as follows (for instance):
> declare @.s varbinary(20), @.out varchar(20)
> select @.s = first
> from sysindexes
> where id = 4
> select @.s
> exec master..sp_hexadecimal @.s, @.out output
> select @.out
> Linchi
> "Mike C#" wrote:
>|||Yeah, change the line
WHILE (@.i < DATALENGTH(@.val))
to
WHILE (@.i <= DATALENGTH(@.val))
<stuart.karp@.gmail.com> wrote in message
news:1156188225.257923.238440@.74g2000cwt.googlegroups.com...
> Hi Mike C#,
> Thanks for the example. However you'll notice that for my example it's
> 2 more than a regular hexadecimal number. For your example it will
> remove the 10 at the end of 0x0000000000000001FFFF07D600002710
> This number is a GUID, and I need to run my stored procedure against
> this exact number, which I want to turn into a string. Is there a good
> way of modifying your code for this longer version that I have. I
> would think that I could also remove 2 zeros at the beginning of my
> GUID, but that's probably not the best idea, since some of my guids use
> the beginning of this, ie something like
> 0xED2CD6A2E653DA4191B3A67272795AE8.
> Anymore help to this? Thanks.
> Mike C# wrote:
>

convert hexadecimal datetime to normal datetime

Hi,
I have a field of timestamp datatype. The data is hexadecimal.
I would like to create a function or query the field so that I can see it
as normal 00:00:00 format?
It would also be nice to be able to query the field by entering a 00:00:00
value but it searches the field in the hexadecimal format and then returns
the results again in the 00:00:00 format.
thanksChris wrote:
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks
Use the undocumented extended sproc xp_varbintohexstr
CREATE FUNCTION dbo.TStoString
(
@.ts binary(8)
)
RETURNS varchar(20)
AS
BEGIN
declare @.s varchar(20)
EXEC master.dbo.xp_varbintohexstr @.ts, @.s out
RETURN @.s
END|||Hi Chris
What do you mean by the normal 00:00:00 format?
A timestamp value has absolutely nothing to do with time. It is an internal
counter. Timestamps are also not meant to be queried. SQL Server compares
them internally to determine if a row has been updated.
If you want a datetime column for your own querying, you can add one to the
table.
HTH
Kalen Delaney, SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Chris,
I think your by the name of the data type. SQL Server has datetime
and timestamp data types, but the second one has nothing to do with datetime
.
Example:
use northwind
go
create table dbo.t1 (
c1 int not null identity(1, 1) unique,
c2 datetime,
c3 timestamp
)
insert into dbo.t1 default values
insert into dbo.t1 default values
select * from t1
update
t1
set
c2 = getdate()
where
c1 = 1
select * from dbo.t1
drop table dbo.t1
go
See datetime and timestamp in BOL for more info.
AMB
"Chris" wrote:

> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Thanks Kalen,
I learned in the books online that is useless to me as an actual way to
determine at what time a record was updated.
"Kalen Delaney" wrote:

> Hi Chris
> What do you mean by the normal 00:00:00 format?
> A timestamp value has absolutely nothing to do with time. It is an interna
l
> counter. Timestamps are also not meant to be queried. SQL Server compares
> them internally to determine if a row has been updated.
> If you want a datetime column for your own querying, you can add one to th
e
> table.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
>
>

convert from hexadecimal to a decimal

We have a tableA with a varchar field whose contents are
hexadecimal. We want to insert these hexadecimal contents
from a varchar field into different table, tableB in
decimal format.
We tried to use Cast and convert functions to explicitly
convert into int field thinking it will give us right
decimal, which didn't work.
Example of what works:
Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
int) returns value of 42.
This doesn't work:
Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
int)
All the values coming from tableA are in '0x2A' form
because the field is defined as a varchar. We can set the
field name in tableB to whatever we want (int, decimal or
even varbinary)
Could someone please pointers / suggestions on how to
convert hexadecimal to int or even how to remove the
leading and trailing ' ?
Thanks in advance
Skip,
This is not too efficient, but it should do the trick. It doesn't
validate the input at all, either.
create function hexchar(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.n = 0
declare @.digits char(16)
set @.digits = '0123456789ABCDEF'
set @.b = substring(@.b,3,8)
while len(@.b) > 0 begin
set @.n = 16*@.n + charindex(substring(@.b,1,1),@.digits)-1
set @.b = substring(@.b,2,8)
end
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
-- Steve Kass
-- Drew University
-- Ref: 8EB8CE54-6E8E-47C1-93AB-35AB3F7C27F5
Skip wrote:

>We have a tableA with a varchar field whose contents are
>hexadecimal. We want to insert these hexadecimal contents
>from a varchar field into different table, tableB in
>decimal format.
>We tried to use Cast and convert functions to explicitly
>convert into int field thinking it will give us right
>decimal, which didn't work.
>
>Example of what works:
>Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
>int) returns value of 42.
>This doesn't work:
>Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
>int)
>All the values coming from tableA are in '0x2A' form
>because the field is defined as a varchar. We can set the
>field name in tableB to whatever we want (int, decimal or
>even varbinary)
>Could someone please pointers / suggestions on how to
>convert hexadecimal to int or even how to remove the
>leading and trailing ' ?
>Thanks in advance
>
>
|||On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Very good, and how about this?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>How about using a numbers table to speed it up?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> declare @.n bigint
> set @.b = substring(@.b,3,8)
> set @.n = (select
>sum((charindex(left(right(@.b,n),1),'0123456789ABC DEF')-1)*POWER(16,(n-1)))
> from dbo.numbers
> where n between 1 and len(@.b))
> return
> case when @.n >= 0X80000000
> then @.n - 0x0100000000
> else @.n end
>end
>go
>
>Best, Hugo
>
|||On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:

>Very good, and how about this?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> return (
> select
>sum((charindex(right(left(@.b,N),1),'123456789ABCD EF'))*POWER(16.,len(@.b)-N))
> from numbers
> where n between 3 and len(@.b))
> - case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
> then 0x0100000000 else cast(0 as bigint) end
>end
>SK
Hi Steve,
Nice!
With the added advantage that it can be used inline in the query, so that
the overhad of calling a function is no longer incurred. (Though I would
comment it if I used it in a query <g>)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You mean this isn't self-documenting? ;)
select
'0x[89abcdef]' lower, '[0-9abcdef]' upper,
0x100000000 parsename, '123456789ABCDEF' power,
cast(0 as bigint) replicate, 16. stuff,
7 charindex, 3 rtrim
into ltrim
select
b, sum(charindex(right(left(b,N),1),power)
* power(stuff,len(b)-N))
- case when lower(b) like lower+replicate(upper,charindex)
then parsename else replicate end
from T, numbers, ltrim
where n between rtrim and len(b)
group by b, lower, upper, parsename, replicate, power, charindex
go
SK
Hugo Kornelis wrote:

>On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>Nice!
>With the added advantage that it can be used inline in the query, so that
>the overhad of calling a function is no longer incurred. (Though I would
>comment it if I used it in a query <g>)
>Best, Hugo
>
|||On Fri, 01 Oct 2004 18:10:12 -0400, Steve Kass wrote:

>You mean this isn't self-documenting? ;)
<snort>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi all,
when i run this query, it will result this :
Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
Invalid column name 'n'.
May i know the 'n' factor is ?

Quote:

Originally posted by Hugo Kornelis
On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

|||On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:

>hi all,
>when i run this query, it will result this :
>Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
>Invalid column name 'n'.
>May i know the 'n' factor is ?
Hi landung,
It's a column in the numbers table I used for this query.
If you don't have a numbers table yet, check out this link:
http://www.aspfaq.com/show.asp?id=2516
If you do have a numbers table, but with another column name, change the
query to reflect the names of your numbers table and column.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Don't forget that if you want something that can be put inline without
a UDF call, you can use something more like this:
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:
>
>
>Hi landung,
>It's a column in the numbers table I used for this query.
>If you don't have a numbers table yet, check out this link:
>http://www.aspfaq.com/show.asp?id=2516
>If you do have a numbers table, but with another column name, change the
>query to reflect the names of your numbers table and column.
>Best, Hugo
>