[Resolved] Checking bits in MySql

[dummy57]

I have a field with a binary value in it, say, a string of 30 one's and zero's of any combination. I want to issue a query that will check a specific bit of this binary string and test to see if it's a one or a zero. How might I go about doing this? I've read about a regular expression function within MySql but is this the best way of doing this?

[Pig]

Depends on how you would need to identify that "bit". Is it always in the same location (eg: charaters 25-28)? Is it only if certain characters around it are a certain specific value?

There is no way to tell you without knowing exactly how they are identified.

[tekky]

function check_bit($bin, $bit)
{
     if($bit < 0 || $bit > strlen($bin)) { return -1; } // bit out of range
     else if(strlen($bin) != (substr_count($bin, "1")+substr_count($bin, "0"))) { return -1; } // invalid bit - non 0/1 bits set
     else { return substr($bin, $bit, 1); }
}

something that simple should work.... then to check your bit...

1's: if (check_bit($mybitvar, $mybitnum)) { do this for a value of 1; }

0's: if (!check_bit($mybitvar, $mybitnum)) { do this for NOT value of 1 ie a return of 0; }

HTH

edit: added some error checking into it... hopefully the substr_count check works... couldnt think of a better way to do it....

[dummy57]

The MySql field would be a varchar field probably around 90 characters in length. The field value would always be the maximum length (in this case 90 characters). I realize that I can do this in PHP but I was wondering if there was a way to do it in MySql so that I can filter some records out in my SELECT query.

For example, say that one record has a value of '...01101011' and I want to check it to see that the second character from the end is a 1. Is there a way I can do that in my SELECT query?

[Pig]

Your initial assumption of using regex is correct. I don't believe there is any better way to do it.

[tekky]

so how did you solve this? SQL regexp? if so, can you post? I'm curious 🙂