Question on data type conversion

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

Transact-SQL (2005)

Question on data type conversion

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-03 : 10:22:00

Hi,

I am trying to insert the following . I tried both ways but I get the below error for each query in the order below..

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '0x0000000000000028' to data type int.

Conversion failed when converting the varchar value '0x0000000000000028' to data type int.

SELECT DISTINCT
ClientID = CONVERT(bigint,trd.PPivotalCompanyID)
FROM
Trade trd
WHERE
CONVERT(int,trd.PPivotalCompanyID) IN (SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST

This is the output for the below query

select PARAMLIST_KEY, PARAM_VALUE, PARAM_PERSIST from MARPTGEN_PARAMLIST
where PARAMLIST_KEY = '101'
WHERE PARAMLIST_KEY ='101' )

PARAMLIST_KEY, PARAM_VALUE, PARAM_PERSIST
101 0x0000000000000028 1
101 0x0000060000000007 1
101 0x0000000000000006 1
101 0x0000010000000006 1
101 0x0020000000000007 1
101 0x0020050000000007 1
101 0x0000060000000006 1
101 0x0080020000000007 1
101 0x0020000000000006 1
101 0x0060000000000006 1
101 0x0000000000000007 1

Your help is much appreciated. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 10:41:47

you've to change CONVERT(int,trd.PPivotalCompanyID) also to bigint in WHERE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-03 : 11:04:34

I get the same error. Not sure what I am doing wrong?

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '0x0000000000000028' to data type int.

SELECT DISTINCT
ClientID = CONVERT(bigint,trd.PPivotalCompanyID)
FROM
Trade trd
WHERE
CONVERT(bigint,trd.PPivotalCompanyID) IN (SELECT CONVERT(int,PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY ='101' )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:20:19

is it storing hexadecimal values in varchar format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-03 : 11:54:09

Yes

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 12:05:46

~~Did you want to cast that as 28?~~

NB -- this is ludicrous!
If so then


SELECT CAST(REPLACE('0x0000000000000028', '0x', '') AS INT)

If as binary


SELECT CAST(CAST( '0x0000000000000028' AS VARBINARY(64)) AS INT)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:10:19

how will '0x0000000000000028' be equal to 28 int?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 12:14:16

quote:
Originally posted by visakh16

how will '0x0000000000000028' be equal to 28 int?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yeah -- my bad -- didn't really think of the consequences. Anyway casting to an intermediate VARBINARY works?

Actually -- I'm not sure what is required.

Did you want this represented as

2 * 16 + 8 = 40 decimal?

or did you want a direct cast from character to binary to int?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:18:10

quote:
Originally posted by Transact Charlie

quote:
Originally posted by visakh16

how will '0x0000000000000028' be equal to 28 int?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yeah -- my bad -- didn't really think of the consequences. Anyway casting to an intermediate VARBINARY works?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

but wont give you correct value. wouldnt result be 40?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 12:20:00

quote:
but wont give you correct value. wouldnt result be 40?

Just realised this myself. It would give you an int value that you could turn back into the character representation of a binary 0028 but that's probably worse than useless. :(

It's been a long day.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:27:28

quote:
Originally posted by Transact Charlie

quote:
but wont give you correct value. wouldnt result be 40?

Just realised this myself. It would give you an int value that you could turn back into the character representation of a binary 0028 but that's probably worse than useless. :(

It's been a long day.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

you could create a function like


CREATE FUNCTION HexStrToInt
 (
 @HexVal varchar (30)
 )
 RETURNS int
 AS
 BEGIN
DECLARE @IntVal int

SELECT  @HexVal=REPLACE(@HexVal,'0x','')

select @IntVal=
sum(
      case lower( substring( reverse(@HexVal), 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
      end *  power( cast(16 as bigint), number - 1 )
)
from master..spt_values n
where type='p'
and  number between 1 and len( @HexVal )

RETURN @IntVal
END
 
and use it like

 select dbo.HexStrToInt('0x0000000000000028')

output
--------------------
40

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources