MySQL String Comparison with Percent Output (Position Very Important

I am trying to compare two entries of 6 numbers, each number which can either can be zero or 1 (i.e 100001 or 011101). If 3 out of 6 match, I want the output to be .5. If 2 out of 6 match, i want the output to be .33 etc.

Note that position matters. A match only occurs when both entries have a 1 in the first position, both have a 0 in the second position etc.

Here a开发者_StackOverflow社区re the SQL commands to create the table

CREATE TABLE sim
(sim_key int,
 string int);

INSERT INTO sim (sim_key, string)
VALUES (1, 111000);

INSERT INTO sim (sim_key, string)
VALUES (2, 101101);

My desired output to compare the two strings, which share 50% of the characters, and output 50%.

Is it possible to do this sort of comparison in SQL? Thanks in advance

Have a look at this example.

CREATE TABLE sim     (sim_key int,      string int);
INSERT INTO sim (sim_key, string)     VALUES (1, 111000);
INSERT INTO sim (sim_key, string)     VALUES (2, 101101);

select a.string A, b.string B,
   sum(case when Substring(A.string,Pos,1) = Substring(B.string,Pos,1) then 1 else 0 end) Matches,
   count(*) as RowCount,
   (sum(case when Substring(A.string,Pos,1) = Substring(B.string,Pos,1) then 1 else 0 end) /
   count(*) * 100.0) as PercentMatch
from sim A
cross join sim B
inner join (
    select 1 Pos union all select 2 union all select 3
    union all select 4 union all select 5 union all select 6) P
        on P.Pos between 1 and length(A.string)
where A.sim_key= 1 and B.sim_key = 2
group by a.string, b.string

It is crude and probably included more than required but shows how it can be done. It is better to create a numbers table with just numbers from 1 to 1000 or so, that can be used repeatedly in many queries where a number sequence is required. Such a table will replace the (select .. union virtual table used in the inner join)

Instead of keeping 10010101 as integer convert this binary version to true integer when compare use bit logic AND, result convert to binary and count '1' to how many match...

for convert: http://dev.mysql.com/doc/refman/5.5/en/binary-varbinary.html

for compare: http://dev.mysql.com/doc/refman/5.5/en/bit-functions.html bitwise AND

...