Bitwise Value Interpretation in SQL from a character based source using like statements

This code is an example of bitwise operators in SQL that are being derived from a well formed data source where the bit positioning is contained as character values of a specific length and shape.  In this case we receive a document that gives us values of specific attributes a person has, and those attributes are stored in a bitwise field where an attribute will correspond to a 1 in a specific position in a field.

/*

Christopher Harsch

Simple example for bitwise operators in SQL.  This is an example, it may not be the bbst, but it is certainly a workable approach.  This uses simple operators that work with strings, which is likely what will be received from a vendor

or external partner.

There specialized operators in SQL for this, but those operators work against non string types.  Which is likely

why you are here, vs another page.

*/

DROP TABLE IF EXISTS #tableofbitwisemasktranslations;

CREATE TABLE #tableofbitwisemasktranslations
             (
                          id           int IDENTITY(1,1) ,
                          bitwisemask  char(4) ,
                          bitwisevalue varchar(255)
             );

INSERT INTO #tableofbitwisemasktranslations
            (
                        bitwisemask ,
                        bitwisevalue
            )

SELECT '1___'        AS BitWiseMask ,

       'Good Enough' AS BitWiseValue

UNION ALL

SELECT '_1__'         AS BitWiseMask ,

       'Smart Enough' AS BitWiseValue

UNION ALL

SELECT '__1_'       AS BitWiseMask ,

       'Well Liked' AS BitWiseValue

UNION ALL

SELECT '___1'          AS BitWiseMask ,

       'The Quiet One' AS BitWiseValue;

 

DROP TABLE IF EXISTS #tmp_example_table_withdata;

 

CREATE TABLE #tmp_example_table_withdata

             (
                          id               INT IDENTITY(1,1) ,
                          thebitwisecolumn CHAR(4)
             );

INSERT INTO #tmp_example_table_withdata
            (

            thebitwisecolumn

            )

          SELECT '0100'

UNION ALL

SELECT '0110'

UNION ALL

SELECT '0101'

UNION ALL

SELECT '1100'

UNION ALL

SELECT '0000'

UNION ALL

SELECT '0110'

UNION ALL

SELECT '1111';

-- Note, this whole process should be to ONLY be creating this data into a proper
-- normalized schema. You should _NOT_ be doing this as a report, or use the data as
-- part of a where criteria.  Put this into a proper 1 to many table, and then filter against that later
-- in your reporting.
--- Example 1 - Get the data as discrete values

SELECT      *
FROM        #tmp_example_table_withdata a
OUTER apply
            (
                   SELECT Trim(Isnull(b.bitwisevalue,'')) AS ValuesAsCSV ,
                          b.id
                   FROM   #tableofbitwisemasktranslations b
                   WHERE  1=1
                   AND    a.thebitwisecolumn LIKE b.bitwisemask ) theData;

--Example 2 - Put the data into a commma separated value list

SELECT      *
FROM        #tmp_example_table_withdata a
OUTER apply
            (
                   SELECT String_agg(Trim(Isnull(b.bitwisevalue,'')) ,', ') AS ValuesAsCSV
                   FROM   #tableofbitwisemasktranslations b
                   WHERE  1=1
                   AND    a.thebitwisecolumn LIKE b.bitwisemask ) theData;