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
)
(
bitwisemask ,
bitwisevalue
)
SELECT '1___' AS BitWiseMask ,'Good Enough' AS BitWiseValueUNION ALLSELECT '_1__' AS BitWiseMask ,'Smart Enough' AS BitWiseValueUNION ALLSELECT '__1_' AS BitWiseMask ,'Well Liked' AS BitWiseValueUNION ALLSELECT '___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)
);
id INT IDENTITY(1,1) ,
thebitwisecolumn CHAR(4)
);
INSERT INTO #tmp_example_table_withdata
(
(
thebitwisecolumn
)
SELECT '0100'
UNION ALLSELECT '0110'UNION ALLSELECT '0101'UNION ALLSELECT '1100'UNION ALLSELECT '0000'UNION ALLSELECT '0110'UNION ALLSELECT '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;
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;
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;
No comments:
Post a Comment