March 2, 2012 at 6:10 am
Hi All,
I need to extract a value stored in Byte 1 of an int field (32-bit). I don't really know a great deal about bit masking in SQL Server so I'm struggling to get an answer that makes much sense. I need to extract the value from byte 1 but I'm not sure how to mask off the other 3 bytes. I *think* it needs to be something like this:
SELECT device_status_flags AS RawData,
device_status_flags & /*something that masks bytes 0, 2 & 3*/ != 0 AS Byte1Value
FROM dbo.MyTable
Am I on the right track or totally heading in the wrong direction?
Here are some sample values from my integer field called device_status_flags:
-539042045
-555819520
-555819520
-505815552
-505815808
-589767936
-522526976
-555819520
-506667776
-505749760
-522396672
-505684224
-505684224
-505684480
-505684224
-505684480
-505684480
-505749760
-505684480
-505684224
-505684480
-505684480
-505684480
-505684480
-505684224
-505618944
-505684224
-505684736
-505684224
-505684224
-505618944
-505684224
-505618688
-505684480
-505684480
-505618688
-505618688
-505618944
-505618944
-505618688
-505618688
-505618688
-488841472
-488710656
-505618688
-876822016
-960970240
-488907264
-488841728
-960773376
-876559616
-488841216
-488775936
-859716096
-826226688
-488775936
-539173376
-842871808
-505618688
-488841216
-826226944
-825899008
-488841216
-488840960
-842676480
-826030080
-488841472
-488841728
-808990208
-825832704
-488841472
-539107584
-488907008
-775434752
-741750016
-488841216
-488841728
-775303168
-758526464
-488841472
-488841472
-758461696
-674443520
-488906752
-488972544
-573648128
-540222720
-488841472
-724773888
-488841472
-488906752
-488840960
-488841216
-489299712
-556280064
-404757248
-404626688
-404756992
-404626432
-404560640
-454761472
-404822784
-404822528
-404626688
-404756992
-404691968
-387980032
-404625920
-404625920
-404625920
-404560896
-404625920
-404560896
-404560128
-404560384
-404560128
-404953600
-505684480
-488841984
-505684480
-505750016
-505684224
-505684736
-505684224
-505684480
-505749760
-522920448
-505882109
-522526976
-505684224
-505684480
-522526976
-522461440
-506405120
-505816064
-505684224
-505684480
-522592512
-505684480
-371005952
-454761472
-454761472
-371071488
-505815552
-505815552
-387783168
-371006208
-522592512
-454761472
-454761472
-454761472
-522592768
-371464704
-454761472
-371006208
-522461440
-522461696
-387717632
-437918464
-371071488
-505684480
-454761472
-522461696
-387782912
-437918464
-437918464
-387783424
-505684992
-505684224
-371071744
-371005696
-522462208
-421075456
-454761472
-454761472
-371005952
-505684992
-421075456
-371005952
-505684480
-421075456
-471604480
-437918464
-371071488
-505684224
-505684224
-370940160
-437918464
-421075456
-371530240
-505684736
-505750016
-387782912
-371005696
-522461696
-437918464
-371464704
-522526976
-437918464
-421075456
-522461952
-371005696
-370940416
-522527232
-437918464
-522461440
-388176384
-421075456
-405019392
-522461696
-505684992
-505684224
-522461696
-522461440
-522462208
-522461952
-522461696
-522527488
-505815808
-556343808
-488841472
-573057280
-539239424
-488775680
-488840960
-505750272
-505684736
-488841216
-488775680
-505750528
-522527232
-488775680
-488841728
-505684736
-522593024
-488775936
-488841472
-539697920
-590031104
-488907008
-505683968
-488841472
-505749504
-505618688
-505618688
-505618688
-505618688
-505618688
-505618688
-505618688
-506011904
-388176896
-556344576
-371006464
-371072256
-371005952
-405084672
-454761472
-404560384
-454761472
-404560384
-404560896
-404560896
-404560896
-404626432
-387848704
-404757504
-371137536
-454761472
-387979776
-404691968
-405020160
-404953600
-404625920
-404953600
-421862400
-488973056
-540484864
-539501568
-539305216
-539370496
-539895040
-556213248
-556737792
-556868864
-556868864
-539238912
-522462208
-539960320
-539370752
-539369984
-539370496
-557196800
-556279296
-539895040
-438705408
-422059264
-539763456
-556869120
-556279296
-539370240
-438377984
-421534720
-573056768
-556147712
-556147712
-573253376
-421797376
-522133504
-556279040
-421534976
-573973760
-573122304
-421534976
-556147968
-556148224
-438311936
-589833728
-505290496
-556344576
-607004928
-438311936
-438508800
-590292992
-556213760
-572990720
-607201792
-422452480
-538976512
-538976512
-606808576
-455155456
-573056512
-590423552
-421535232
-606873856
-607594496
-421600768
-589965056
-573122048
-421666048
-606873856
-623651328
-438836224
-590227200
-640559104
-422452736
-624110336
-674115072
-640625664
-439426560
-438705408
-641214976
-555819520
-640560128
-455220736
-455679744
-640560128
-640691200
-455286528
-472653568
-691678208
-725036800
-456334848
-724972800
-757935616
-471997952
-455614208
-455286784
-472064512
-456203776
-489496320
-556148480
-488840960
-488906496
-488840960
-505618432
-555819520
-488841216
-488841472
-488907264
-589899776
-590095872
-488841472
-488907008
-590031360
-590031360
-472129536
-472130048
-590096896
-590031104
-488841216
-555819520
-606743040
-488841728
-489365760
-606939392
-606808576
-488841472
-488775680
-673982976
-724249600
-488907008
-506077440
-556280064
How would I extract the value stored in Byte 1 of the above field? Any general tips about bitmasking would be very much appreciated as I'm very much in the dark at the moment!
TIA,
Chris
March 2, 2012 at 6:20 am
i think this is going to get what you are after; i'm returning true/false based on the value of the first 4 bytes; with that i think you can see how to map all 32 of them if needed.
with myValues(val)
AS
(
SELECT '-539042045' UNION ALL SELECT '-555819520' UNION ALL SELECT '-555819520' UNION ALL SELECT '-505815552' UNION ALL
SELECT '-505815808' UNION ALL SELECT '-589767936' UNION ALL SELECT '-522526976' UNION ALL SELECT '-555819520' UNION ALL
SELECT '-506667776' UNION ALL SELECT '-505749760' UNION ALL SELECT '-522396672' UNION ALL SELECT '-505684224' UNION ALL
SELECT '-505684224' UNION ALL SELECT '-505684480' UNION ALL SELECT '-505684224' UNION ALL SELECT '-505684480'
)
SELECT 'value: ' + [val] + CHAR(13) +CHAR(10) +
'Byte 1 '+ MIN(CASE val & 1 WHEN 1 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(10) +
'Byte 2 '+ MIN(CASE val & 4 WHEN 4 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(10) +
'Byte 3 '+ MIN(CASE val & 8 WHEN 8 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(10) +
'Byte 4 '+ MIN(CASE val & 16 WHEN 16 THEN 'True' ELSE 'False' END) + CHAR(13)+ CHAR(10)
FROM myValues
GROUP BY [val]
Lowell
March 2, 2012 at 6:28 am
Lowell, I think you're confusing bits with bytes.
Chris, is Byte 0 the eight most significant (highest value) bits, or the eight least significant? I think you need an "OR" operation (|) instead of an "AND" (%).
John
March 2, 2012 at 6:30 am
John,
In this instance, Byte 0 is the Least Significant.
Here is the description of the encoding that I have been given:
Byte 0 (LSB) = status flags
Byte 1 Current voltage (the value I want to extract)
Byte 2 Minimum voltage since last mid-night
Byte 3 (MSB) Maximum voltage since last mid-night
Thanks,
Chris
March 2, 2012 at 6:59 am
Is it just as simple as this:
declare @device_status_flag int=-539042045
SELECT SUBSTRING(CAST(@device_status_flag AS BINARY(4)),2,1)
This result could obviously be cast back to an int if required.
March 2, 2012 at 7:27 am
Howard, that is brilliant!
I had to tweak it slightly as follows to get the Byte I wanted but the method works perfectly. Here's the what I ended up with:
(CAST(SUBSTRING(CAST(device_status_flags AS BINARY(4)), 1, 1) AS INT) * 8) + 1700) * 0.001 AS Voltage
Thank you all for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply