Solved

SQL Server Convert or Cast function

Posted on 2012-03-22
8
455 Views
Last Modified: 2012-03-27
I am trying to convert a decimal into a hexadecimal string in a table.  I can run the select query just fine (Select convert(varbinary(8), account) from accountTemp) but cannot run either an update on the table or a new insert to store the value permanently. (update accountTemp set account = convert(varbinary(8), account) -- OR -- Insert into account select convert(varbinary(8),account) from accountTbl

Any suggestions as to how I can have this be converted and stored into the table?
0
Comment
Question by:PhillipsPlastics
8 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37752608
Try doing it through a variable?


declare @tmp VarBinary(8)
set @tmp = (Select convert(varbinary(8), account) from accountTemp)
update accountTemp set account @tmp
where
-- ...  fill in where ... --
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37752611
Look at the definition of Convert. You're changing the type from whatever Account is into a varbinary type. I'll bet you Account is not a varbinary type of field!

Take a look at http://rextang.net/blogs/work/archive/2008/01/13/5855.aspx for info on converting your varbinary out into hex.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37752658
Please try:

master.dbo.fn_varbintohexstr

For example:

SELECT master.dbo.fn_varbintohexstr(convert(varbinary(8), account))
from accountTemp
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:PhillipsPlastics
ID: 37753087
Well this is what I ended up doing since nothing else was working...

declare @tmp varbinary(8);
set @tmp = (CONVERT(varbinary(8),66082));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66082;
set @tmp = (CONVERT(varbinary(8),514));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 514;
set @tmp = (CONVERT(varbinary(8),544));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 544;
set @tmp = (CONVERT(varbinary(8),512));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 512;
set @tmp = (CONVERT(varbinary(8),66080));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66080;
set @tmp = (CONVERT(varbinary(8),66048));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66048;
set @tmp = (CONVERT(varbinary(8),546));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 546;
set @tmp = (CONVERT(varbinary(8),66050));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66050;
select * from phonesearch_db.dbo.accountTemp;

Open in new window

0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37753228
Did fn_varbintohexstr not work for you?
0
 

Accepted Solution

by:
PhillipsPlastics earned 0 total points
ID: 37753405
It seemed to do the same thing as convert but what I am actually ending up doing is setting the column account to be varbinary(8) and when I read the data into the table it automatically converts the decimal to hex.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37754514
Clever! And there we all were assuming that you needed to keep the original value. Thanks for wrapping it up.
0
 

Author Closing Comment

by:PhillipsPlastics
ID: 37770419
None of the other options listed worked.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question