Convert a binary representation of a number stored as VARCHAR to INT

I'm working with a SQL Server database which has a column containing a binary number.

However, the binary number is stored as a VARCHAR.

I need to convert the VARCHAR into its numerical representation so I can do some further calculations.

For example I want to convert the string "0010" to the numeric value 2.

However trying to CAST the string as an int gives me the value 10 instead.

Any suggestions for how I can make this conversion in SQL?

Solution

Some great answers posted here by other users, but unfortunately none of them worked for me because they relied on CREATE FUNCTION which I don't have permission to execute on this system.

In the end, I came up with an ugly approach which works - breaking down each digit of the "week pattern string" and multiplying by the relevant power of 2. The following example shows how this works for an 8 character week pattern:

SELECT
    ACTIVITIES.Name AS ActivityName,
    ACTIVITIES.WeekPattern AS WeekPattern,
    SUBSTRING(ACTIVITIES.WeekPattern,8,1) *   1 +
    SUBSTRING(ACTIVITIES.WeekPattern,7,1) *   2 +
    SUBSTRING(ACTIVITIES.WeekPattern,6,1) *   4 +
    SUBSTRING(ACTIVITIES.WeekPattern,5,1) *   8 +
    SUBSTRING(ACTIVITIES.WeekPattern,4,1) *  16 +
    SUBSTRING(ACTIVITIES.WeekPattern,3,1) *  32 +
    SUBSTRING(ACTIVITIES.WeekPattern,2,1) *  64 +
    SUBSTRING(ACTIVITIES.WeekPattern,1,1) * 128 AS IntegerRepresentation
FROM
    ACTIVITIES;

This gives the following style of output:

ActivityName	WeekPattern	IntegerRepresentation
MATH101/LEC	00001111	15
PHYS101/LEC	11111111	4095
SPAN101/TUT	10000001	2049