Convert string built bit map (100101) to int value in SQL

Question

Is it possible, and how would I go about converting a string representation of a bit mapping such as '10010' to cast it as the integer value it represents? We are using a string built bit map for permission settings, and I need to then cast it to the integer value, after making the string. We check per permission, so it user is assigned to it, it gets a 1, and if not, it gets a 0. Then then program compares the users to the bit map, and see's if they are set to the right or not.

Currently, the amount of rights can vary, so we take how many rights there are, and then pad the string to next binary size of 8. So if there are 7 rights, we make a 8 character length string, or if there are 12 rights, we make a 16 character leng string.

I need to find a way to just convert string '100101' to int for example. Is this possible?

Answer 1

Try this

SELECT CONV( BINARY( '100101' ) , 2, 10 )

It will do the trick. I was inspired by Convert a binary to decimal using MySQL

EDIT

In fact you can make it even more simple by using just CONV

SELECT CONV( '100101' , 2, 10 )

See http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_conv for more details