lundi 6 février 2023

Varbinary search with min and max pattern type

So 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 binary spaces.

What I've tried:

DECLARE @t TABLE (
    val VARBINARY(MAX)
)

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(nax)
set @pattern = 0x0001
set @pattern2 = @pattern+0xFF

select @pattern,@pattern2

SELECT
    *
FROM @t
WHERE val<@pattern
OR val>@pattern2

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), attr) = 0xdata

also this:

select * 
from table
where CONVERT(varchar(max),attr,2) like '%data%'

Which works great for searching exact patterns, but not for ranges, I need some combination of both.

I'm aware I could technically add every possible outcome 1 by 1 and let it cycle through all the listed possibilities, but there has to be a smarter way. Goals:

  1. Locating the prefix
  2. 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 '96 00' or '26 96 00'. 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.

I'm aware this explanation is pretty dire, but bear with me, thanks.

Aucun commentaire:

Enregistrer un commentaire