Conversion Failed Nvarchar to INT

Question

I have two tables called Entry_Data and Data

Entry_Data has three columns:

EntryID DataID DataTypeID

1 50 18

2 49 59

30 28 16

Data has two columns:

DataID Value

50 0x00000000033654

49 Removable

28 E:\Test.txt

The Value column is an nvarchar field.

I have written a left join while attempting to convert all Value fields that start with 0x to an int so that the Hexadecimal value can be converted into a meaningful and human readable value however I get the error:

Conversion failed when converting the nvarchar value ‘0x00000000033654’ to data type int

SELECT TOP 100
  Entry_Data.DataTypeID AS DataTypeID,
  CAST(Data.Value AS nvarchar(440)) AS Value,
  Data.DataID AS DataID
FROM ActivityLog.Entry_Data
LEFT JOIN ActivityLog.Data
  ON ActivityLog.Entry_Data.DataID =
                                    CASE
                                      WHEN DataTypeID = 18 AND
                                        Value LIKE '%0x%' THEN CONVERT(nvarchar, CONVERT(int, Value, 1))
                                      ELSE DataTypeID
                                    END
WHERE DataTypeID = 18

You getting the error because you are comparing an int value with nvarchar in `JOIN` condition. — Jibin Balachandran, Oct 10 '17 at 09:16
You are trying to check `DataID` with `Value` i.e 50 to `0x00000000033654` which doesn't make sense. — Jibin Balachandran, Oct 10 '17 at 11:16
Unfortunately that is how the tables are set out so i cannot to a join on anything else. Are you saying that what i am attempting to achieve is impossible? — Oliver843, Oct 10 '17 at 12:13
Maybe, you can see this question: https://stackoverflow.com/questions/703019/convert-integer-to-hex-and-hex-to-integer — Joe Taras, Oct 10 '17 at 12:43

score 0 · Answer 1 · edited Oct 10 '17 at 08:36

0

In your case expression, you are returning an nvarchar for one situation

 THEN CONVERT(nvarchar, CONVERT(int, Value, 1))

and an integer for the second situation

ELSE DataTypeID

You need to correct your join so it always return and int for the join to work.

edited Oct 10 '17 at 08:36

Yogesh Sharma

49,870
5
26
52

answered Oct 10 '17 at 08:34

NorthernMonkey

34
3

The Value field will not always be an INT so would ELSE Value work? Thanks for the reply – Oliver843 Oct 10 '17 at 08:40
I think you have possible misused the join statement here. the join would always be on the Data_ID columns, your case expression for making the results readable should be part of the select statement. – NorthernMonkey Oct 10 '17 at 08:42
Thank you for your reply. Could you give me an example please? I am not a native SQL writer – Oliver843 Oct 10 '17 at 08:57
Unless I have misread your question, something like this might be what you want: `SELECT TOP 100 Entry_Data.DataTypeID AS DataTypeID, CAST(Data.Value AS nvarchar(440)) AS Value, CASE WHEN DataTypeID = 18 AND Value LIKE '%0x%' THEN CONVERT(nvarchar, CONVERT(int, Value, 1)) ELSE DataTypeID END as FormattedValue, Data.DataID AS DataID FROM Entry_Data LEFT JOIN Data ON Entry_Data.DataID = Data.DataID` – NorthernMonkey Oct 10 '17 at 09:10
Hi, The Formatted Value = DataTypeID when i search 1000 rows but DataTypeID does not equal 18 in any of them. I added a WHERE clause to equal DataTypeID and i got Conversion failed when converting the nvarchar value ‘0x00000000033654’ to data type int. – Oliver843 Oct 10 '17 at 09:21
Sorry, I didn't update the case statement before I put the comment in. Something like this should work, the conversion is working here SELECT TOP 100 Entry_Data.DataTypeID AS DataTypeID, CAST(Data.Value AS nvarchar(440)) AS Value, CASE WHEN DataTypeID = 18 AND Value LIKE '%0x%' THEN SELECT CONVERT(nvarchar,CONVERT(int, CONVERT(varbinary, Data.Value, 1))) ELSE Data.Value END as FormattedValue, Data.DataID AS DataID FROM Entry_Data LEFT JOIN Data ON Entry_Data.DataID = Data.DataID – NorthernMonkey Oct 10 '17 at 09:31

Conversion Failed Nvarchar to INT

1 Answers1