sqlormfull-text-searchrelational-databasevector-database

In SQL, how to search for rows by binary data prefix?

For example, I want to search for all rows that starts with \x00\xff\xaa in a binary data column. I am writing this pseudo-code in C# Entity Framework:

            IEnumerable<KeyValue> keyValues = Db.KeyValue
                .Where(kv => kv.Key.KeyBytes.Take(key.Length).SequenceEqual(key));

I know this should not work well. But is there any way I can search by binary prefix in any relational database or any ORM?

Solution

  • In MySQL you can use like 'yourprefix%'

    Example:

    CREATE TABLE t (c BINARY(10));

INSERT INTO t SET c = '\x00\xff\xaaa';
INSERT INTO t SET c = '\x01\xff\xaab';
INSERT INTO t SET c = '\x02\xff\xaac';
INSERT INTO t SET c = '\x00\xff\xaad';

select * from t where c like '\x00\xff\xaa%';

    Result:

    c
x00xffxaaa
x00xffxaad

    Because b and c did not have that prefix.

    Your ORM seems to be EF for .NET. If so, then you can use .StartsWith.

    So, to put it into general terms: