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
>> >
>
Showing posts with label str. Show all posts
Showing posts with label str. Show all posts
Sunday, February 19, 2012
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:
>
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:
>
Sunday, February 12, 2012
Convert Decimal to String is without rounding up
Hello I'm trying to write a SQL Statement along the lines of...
SELECT stringField + ' : ' + STR(decimalField) AS myField FROM tablename WHERE myCondition = myValue
Where stringField is a String field and decimalField is a Decimal Field in my Table.
In this statement it converts the decimal field to a string value so that it doesn't throw a conversion error but unfortunatly it seems to round up the value to an integer value and cuts off all my decimal places.
How can I get it to keep the Decimal Places?STR(decimalField,6,2)|||Thanks Man appreciated one other question.
When I do a "SELECT decimalField FROM table" and ouput it to HTML it always outputs as 22.4500 or 132.220 etc. I could easilly remove the 0's on the right using the code behind my HTML but can I do this in SQL instead?
I'm guessing it's done in a similar way to the above solution?
Thanks for you help so far.|||SELECT CAST(decimalField AS INT) AS NonDecimalField FROM table|||declare @.i decimal(20,10)
set @.i = 132.2200
select @.i, ltrim(str(@.i, 10, 2))|||Thanks guys.
Sorry derrick - that removed all decimal places - all I wanted was to remove any preceding zeros.
rdjabarov - that worked fine thanks
SELECT stringField + ' : ' + STR(decimalField) AS myField FROM tablename WHERE myCondition = myValue
Where stringField is a String field and decimalField is a Decimal Field in my Table.
In this statement it converts the decimal field to a string value so that it doesn't throw a conversion error but unfortunatly it seems to round up the value to an integer value and cuts off all my decimal places.
How can I get it to keep the Decimal Places?STR(decimalField,6,2)|||Thanks Man appreciated one other question.
When I do a "SELECT decimalField FROM table" and ouput it to HTML it always outputs as 22.4500 or 132.220 etc. I could easilly remove the 0's on the right using the code behind my HTML but can I do this in SQL instead?
I'm guessing it's done in a similar way to the above solution?
Thanks for you help so far.|||SELECT CAST(decimalField AS INT) AS NonDecimalField FROM table|||declare @.i decimal(20,10)
set @.i = 132.2200
select @.i, ltrim(str(@.i, 10, 2))|||Thanks guys.
Sorry derrick - that removed all decimal places - all I wanted was to remove any preceding zeros.
rdjabarov - that worked fine thanks
Subscribe to:
Posts (Atom)