CODEWITHSUNDEEP
sql-servert-sql
user6546748
user6546748

Reputation:

T-SQL how to compare varchar alphanumeric value to int value

I am attempting to query relational storage to return objects with a different value reported in a packet (broadcast.FirmwareVersionReported) compared to the value reported as last delivered (device.firmwareid).

Testing

I attempted to level the playing field by using LEFT to remove the last two characters from the column firmware.softwareid and used CAST to change both to a VARCHAR to allow the comparison. Its clear this approach is not correct however.

select 
    LEFT(CAST(firmware.softwareid as VARCHAR),4) AS softwareid, 
    CAST(broadcast.[FirmwareVersionReported] as VARCHAR) 
from 
    device
inner join 
    broadcast on broadcast.deviceid = device.deviceid
inner join 
    firmware on firmware.firmwareid = device.firmwareid
where
    softwareid <> [FirmwareVersionReported]

Result

Msg 468, Level 16, State 9, Line 7
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the not equal to operation.

Tables

Broadcast

[DeviceId] [int],
[FirmwareVersionReported] [varchar](10),

Firmware

[FirmwareID] [int] IDENTITY(1,1),
[SoftwareID] [varchar](10),
[PacketSoftwareID] [varchar](10),

Device

[DeviceID] [int] IDENTITY(1,1),
[FirmwareID] [int],

Question

Looking for advice on how best to approach resolving this problem please.

Upvotes: 1

Views: 450

Answers (2)

Cato
Cato

Reputation: 3701

enter code here
select 
    LEFT(CAST(firmware.softwareid as VARCHAR),4) AS softwareid_mod, 
    CAST(broadcast.[FirmwareVersionReported] as VARCHAR) 
from device
    inner join broadcast on broadcast.deviceid = device.deviceid
    inner join firmware on firmware.firmwareid = device.firmwareid
WHERE LEFT(CAST(firmware.softwareid as VARCHAR),4) COLLATE DATABASE_DEFAULT <> [FirmwareVersionReported] COLLATE DATABASE_DEFAULT

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23797

Instead of converting to char, convert to int for comparison:

select 
    LEFT(CAST(firmware.softwareid as VARCHAR),4) AS softwareid, 
    CAST(broadcast.[FirmwareVersionReported] as VARCHAR) 
from device
    inner join broadcast on broadcast.deviceid = device.deviceid
    inner join firmware on firmware.firmwareid = device.firmwareid
WHERE floor(cast(softwareid as int)/100) <> cast([FirmwareVersionReported] as int)

Upvotes: 1

Related Questions