I came across some code that stores multiple checkbox values into 1 value in the database.
This works by assigning each value a base 2^n value, and adding all the selected values and storing that result.
I am wondering how to query for records in the database that have a specific value inside the value that is stored in the db.
Example
Checkbox 1 = 2
Checkbox 2 = 4
Checkbox 3 = 8
Checkbox 4 = 16
Lets say checkboxes 1 and 3 are selected. We would store 10 in the database.
How would I query for records the have checkbox 1(value 2) selected in the resulting value that is stored in the database(10)?
This is sql server.
You could use bitwise AND
:
SELECT *
FROM tab
WHERE answer & 10 = 10
Similar approach used on @@OPTIONS:
The @@OPTIONS function returns a bitmap of the options, converted to a base 10 (decimal) integer.
To decode the @@OPTIONS value, convert the integer returned by @@OPTIONS to binary, and then look up the values on the table at Configure the user options Server Configuration Option. For example, if SELECT @@OPTIONS; returns the value 5496, use the Windows programmer calculator (calc.exe) to convert decimal 5496 to binary. The result is 1010101111000. The right most characters (binary 1, 2, and 4) are 0, indicating that the first three items in the table are off.
Checking ANSI_NULLS
To view the current setting for this setting, run the following query:
DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';
SELECT @ANSI_NULLS AS ANSI_NULLS;