Showing posts with label varbinary. Show all posts
Showing posts with label varbinary. Show all posts

Sunday, March 25, 2012

Converting binary to string

is there a way to query a varbinary column and print the readable char in query analyzer?

thanks in advance!

Ryan

No way to do this, because SQL only store the binary data and has no sense for character encoding and structure of the doc.|||

There is a way, here it is...

declare @.pass varchar(30)
declare @.binPass varbinary(8)
set @.binPass = (select password from tblEMP where empid = '123')
set @.pass = (select cast(@.binPass as varchar(20)))
print reverse(@.pass)

I get characters like squares after the text, but I know to disregard them and only look at the text. Anyone know how to get rid of the squares and weird characters? thanks

Ryan

|||

SQL Server will convert varbinary to varchar what it will not convert is varbinary to text. Try the link below for the complete conversion chart and code samples. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

sqlsql

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>sqlsql

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?
convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com. ..
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>

Thursday, March 22, 2012

Converting a Hex String to a Varbinary or vice versa

I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?

? For the opposite, the easiest way is to use the built-in (but undocumented) function, fn_varbintohexstr: DECLARE @.x BINARY(16) SET @.x = NEWID() SELECT master.dbo.fn_varbintohexstr(@.x) ... Going the other way around is not quite so straightforward. I usually use dynamic SQL: DECLARE @.y CHAR(34) SET @.y = '0x499104dc92dd27499da9ad8f56dcb437' DECLARE @.x BINARY(16) DECLARE @.sql NVARCHAR(200) SET @.sql = 'SELECT @.x = ' + @.y EXEC sp_executesql @.sql, N'@.x BINARY(16) OUTPUT', @.x OUTPUT SELECT @.x -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Orphaned Zombie@.discussions.microsoft.com> wrote in message news:76360e15-52e8-499f-87b5-44f9eb1f0579@.discussions.microsoft.com... I'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?|||

Please take a look at the links below for some options:

Integer value to hex

http://www.umachandar.com/technical/SQL6x70Scripts/Main89.htm

Hex to integer

http://www.umachandar.com/technical/SQL70Scripts/Main29.htm

If you don't have a table of numbers (which the 2nd link uses), you can use code like below:

declare @.t varchar (8)
select @.t = 'FFFFFFFF'
select sum( (charindex( lower( substring( hexstr , number + 1, 1 ) ), hexchars ) - 1) * convert( decimal( 28 , 0 ) , power( 16 , number ) )
)
from
(
select reverse( @.t ) as hexstr, '0123456789abcdef' as hexchars
) as h1
cross join
(select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7) as n(number)
go

You can extend this to generate 64-bit integer/bigint values also.

Monday, March 19, 2012

Convert varbinary(16) to nvarchar

Hi,

Basically. I need to convert a varbinary(16) to a varchar in a sorted procedure (to update a table). [in query analyser key looks like 0x00000000000000000001]

Background:
I'm having to output replication commands from the distributor DB and want to store the sequence I'm upto to not have to repeat the same commands on the next export.

The stored proc for performing the export accepts an nvarchar(22) for the min key A(and this is how I've stored it). The records are then exported with varbinary(16) datatyped keys

I need to then store this key back but cannot seem to convert it.

Any thoughts are much appreciated.
Gaz

I've run into similar issues.

Try converting to a BigInt and storing that way. If the datatype is VarBin(16) and you shove in a value of 16, it will convert it. Usually, if an external application is expected a binary type, and you feed it 16 (without the 0x0 in the front) it will do it's own conversion.

So, you have varbin(16) type, value: 0x00000000000000000001

convert Hex value to Bigint (which, in this case would be = 1)

convert the bigint to a string and store the string (in this case "1"). Later when your app gets the string "1", it might just convert it appropriately. In any event, it's pretty easy to test.

Code Snippet

insert into MyTable (MyStringField)

select convert(varchar(32),convert(bigint,<MyHexValue>))

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 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
>> >
>