0

I have record with strings:

record id         value             type
sn      1       aaaa181c1c3e9997   single
sn      2       caca381c1c3e6483   masculine

These are almost 500+,they are stored in mysql which i fetch,converting record to 64bit(bitcout work only integer),they are returned as all queires stored,not most like.I use code:

SELECT * FROM MY_TABLE ORDER BY BIT_COUNT(CAST(CONV(record,16,10) as unsigned integer) ^ CAST(CONV('$submit', 16, 10) AS UNSIGNED) ) LIMIT 1;

It is working fine,requirement is suppose supplied value is aaaa181c1c3e9997,then it display all queries whether it match or not.I just want to fetch only it matches more than 15%,else don't fetch anyone.Please help what i'm missing. I also used like:

SELECT * FROM MY_TABLE ORDER BY BIT_COUNT(CAST(CONV(record,16,10) as unsigned integer) ^ CAST(CONV('$submit', 16, 10) AS UNSIGNED) ) < 15;
3
Contributors
3
Replies
19
Views
2 Years
Discussion Span
Last Post by cereal
0

Have you tried by moving the condition to the WHERE statement? For example:

SELECT * FROM MY_TABLE WHERE BIT_COUNT(CAST(CONV(value,16,10) as unsigned integer) ^ CAST(CONV('$submit', 16, 10) AS UNSIGNED) ) >= 14;

Edited by cereal

0

only fetch columns you need,
LIKE helps you search
LIMIT number of results

SELECT id, value, type FROM MY_TABLE WHERE value LIKE CAST(CONV('$submit', 16, 10) AS UNSIGNED) LIMIT 15;

Edited by Keimpe: code

0

@Keimpe

Hi! You're correct about the select statement, however the original query is performing a bitwise xor between values in table and the submitted input, then the result is read by BIT_COUNT() and compared through >= which is not a limit clause.

So, if my interpretation of the OP request is correct, my previous query should work.

Live example: http://sqlfiddle.com/#!9/233e97/1

Docs about bit count: https://dev.mysql.com/doc/refman/5.7/en/bit-functions.html

Edited by cereal

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.