I'm trying to make a query which goes through varbinary data. The issue is that I can't really finish what I'm trying to achieve. What you should know about the column is varbinary(50) and the patterns that occur have no specific order in writing, meaning every prefix could be anywhere as long it has 3 bytes(0x000000) First byte is the prefix second and third are value data that I'm looking to check if its within the range i like. All the data is written like this.
What I've tried:
DECLARE @t TABLE (
INSERT INTO @t SELECT 0x00000100000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00001000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00010000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00100000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00000f00000000000000000000000000000000000000000000000000
declare @pattern varbinary(max)
declare @pattern2 varbinary(max)
set @pattern = 0x0001
set @pattern2 = @pattern+0xFF
This was total bust the patterns were accurate up to 2 symbols if I were to use 4 symbols as pattern it would work only if the pattern is in predefined position. I've tried combination of this and everything below.
WHERE CONVERT(varbinary(2), val) = 0xdata
where CONVERT(varchar(max),val,2) like '%data%'
Which works great for searching exact patterns, but not for ranges, I need some combination of both.
I need something that would detect this on its own while i just specify a start and end point to look in between like the highest number variation would be '26ffff', but limiting it to something like 'ff00' is acceptable for what I'm looking for.
My best guess is 2 defined numbers, 1 being the allowed max range and 2nd for a cap, so it doesn't go through every possible outcome. But I would be happy to whatever works.
The data origin is related to a game server which stores the data like this. There's the predefined prefixes which are the stat type and the rest of the data is the actual numeric value of the stat. The data is represented by 6 characters data intervals. Here is a sample of the data stream. Its always 6-6-6-6-6 as long there's space to record the data on since its capped at 50 characters.
The groups are always in 3byte fashion, my idea is to use the first byte to narrow down the search and use then use the second 2 bytes to filter it. I just don't know how to pull it off in an effective way. If the 3byte pattern is violated the data becomes unreadable meaning even if you don't need the extra byte you still have to count it otherwise the data breaks example of a working data.
example of a broken data:
The only issue i could think is when the prefix and part of the value are both true example:
Unless the query is specifically ordered to read the data in 3byte sequence meaning it knows that the first byte is always a prefix and the other 2 bytes are value.
Q:Can that be used as an alignment indicator so that the first non-zero byte after at least 3 zero bytes indicates the start of a group?
A:Yes, but that's unlikely I mean it although possible it would be written in order like:
It wouldn't skip forward an entire 3byte group filled with no data. This type of entry would occur if someone were to manually insert it to the db, the server doesn't make records with gaps like those as far I'm aware:
The closest to my desired result as a working query is this, but this is terrible it would work for small ranges, but anything greater would be tedious.
select * from @t
where (CONVERT(varchar(max),val,2) like '%262100%' or
CONVERT(varchar(max),attr,2) like '%262200%' or
- Locating the prefix(first binary data pair)
- Defining a max value after the prefix, everything above that threshold to be listed in the results. Let's say '26' is the prefix, the highest allowed number after is '9600' or '269600'. Basically any data that exceeds this pattern '269600' should be detected example '269700'. or query result would post this:
select * from table where CONVERT(varchar(max),attr,2) like '%269700%'
I'm trying to get a query that can cycle through varbinary data from a table which searches for a specific result within set parameters example:
0x263700, the data is divided in 3-byte segments, 1 byte is the header which will be used for a search pattern, the other 2 bytes are the value I have to check if its within set range for example: everything above '3700'(+ 1 bit difference) binary value until 'FFFF' would be classified as a result or the max plausible result would be '26FFFF'. This isn't for specific data stream to be cycled one by one.