How to perform boolean addition on an SQL select

Question

I have the following data in a database table column named timeSchedule

00100110
00010100
00110000
00110011

Boolean addition would result in

00110111

Is there a way to do this in sql? Something like select sumboolean(timeSchedule) from myTable

Someone asked for DDL+DML.. here is an example:

CREATE TABLE [dbo].[myTable](
    [musPracticeID] [int] IDENTITY(1,1) NOT NULL,
    [chosenDate] [datetime] NULL,
    [timeSchedule] [nvarchar](50) NULL CONSTRAINT [DF_myTable_schedule]  DEFAULT (N'0000000000000000')
)

INSERT INTO myTable (chosenDate, timeSchedule)
      VALUES (’06/07/2015’, ’01000100’);

Answer 1

OK, Now that we have the DDL (unfortunately without the DML but only one row). we can provide a solution :-)

firstly! I highly recommend NOT TO USE the solution above, THERE IS NO NEEDS for loops even you not use fixed data length we know the max len (50).

Secondly! If you are going to parse text then you should use SQLCLR and not looping and parsing using T-SQL, in most cases as this one as well.

Third :-) here is simple example for simple solution. I only used the first 10 chars... you can continue to 50... you can use dynamic query in order to create the query if you dont want to write it yourself manually (There are other solutions as well, I recommend to check execution plan and IO used in order to select the best solution for u):

CREATE TABLE [dbo].[myTable](
    [musPracticeID] [int] IDENTITY(1,1) NOT NULL,
    [chosenDate] [datetime] NULL,
    [timeSchedule] [nvarchar](50) NULL CONSTRAINT [DF_myTable_schedule]  DEFAULT (N'0000000000000000')
)
GO
truncate table [myTable]
INSERT INTO myTable (chosenDate, timeSchedule) 
VALUES 
('06/07/2015', '00100110'),
('06/07/2015', '00010100'),
('06/07/2015', '00110000'),
('06/07/2015', '00110011');
GO

select * from myTable
GO

;With MyCTE as (
    select 
        SUBSTRING([timeSchedule],1,1) as c1,
        SUBSTRING([timeSchedule],2,1) as c2,
        SUBSTRING([timeSchedule],3,1) as c3,
        SUBSTRING([timeSchedule],4,1) as c4,
        SUBSTRING([timeSchedule],5,1) as c5,
        SUBSTRING([timeSchedule],6,1) as c6,
        SUBSTRING([timeSchedule],7,1) as c7,
        SUBSTRING([timeSchedule],8,1) as c8,
        SUBSTRING([timeSchedule],9,1) as c9
    from myTable
)
select 
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c1)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c2)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c3)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c4)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c5)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c6)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c7)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c8)) > 0 THEN 1 ELSE 0 END)+
    CONVERT( NVARCHAR(50),CASE WHEN SUM(CONVERT(INT,c9)) > 0 THEN 1 ELSE 0 END)
from MyCTE

Answer 2

First thing you need is a way to get the string and convert it into a #. So, you need to create a new scalar function (borrowed from here).

CREATE FUNCTION [dbo].[BinaryToDecimal]
(
    @Input varchar(255)
)
RETURNS bigint
AS
BEGIN

    DECLARE @Cnt tinyint = 1
    DECLARE @Len tinyint = LEN(@Input)
    DECLARE @Output bigint = CAST(SUBSTRING(@Input, @Len, 1) AS bigint)

    WHILE(@Cnt < @Len) BEGIN
        SET @Output = @Output + POWER(CAST(SUBSTRING(@Input, @Len - @Cnt, 1) * 2 AS bigint), @Cnt)

        SET @Cnt = @Cnt + 1
    END

    RETURN @Output  

END

Then you can simply use:

SUM([dbo].[BinaryToDecimal](timeSchedule))

Then wrap that in another function to convert it back to a string representation. This is a good example.

By the way, storing binary as a string is almost always the wrong approach.

Answer 3

You can use the following query to perform bitwise OR of each 1, 0 character contained in field [timeSchedule] of your table:

;WITH Tally (n) AS
(   
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0)) b(n)   
), CTE AS (
   SELECT n, MAX(x.c) AS bitwiseOR
   FROM mytable
   CROSS JOIN Tally
   CROSS APPLY (SELECT SUBSTRING([timeSchedule], n, 1)) AS x(c)
   GROUP BY n
)
SELECT ( 
   SELECT CAST(bitwiseOR AS VARCHAR(MAX)) 
   FROM CTE AS t         
   WHERE bitwiseOR <> ''
   ORDER BY n
   FOR XML PATH('')) AS sumBoolean

The idea is to use a tally table in order to 'explode' each character of [timeSchedule] column. Then use MAX to perform bitwise OR operation per bit position. Finally, use FOR XML PATH in order to concatenate all single bits into a string.

Note: This query will work even for variable length values of [timeSchedule], i.e. for any value contained in the column having a length between 1 and 50.

Demo here

Answer 4

Here are two more solutions :-) the logic is the same. But once I saw my solution in practice, i realized that I dont need to use SUM since we just need to chose MAX. Next since the CHAR 1 is more than the char 0 (char and not number) in most collate (cultures) then we don't need any CONVERT as well and we can just select the MAX from the CHAR. so here are the two solutions:

-- This solution fit all
;With MyCTE as (
    select 
        SUBSTRING([timeSchedule],1,1) as c1,
        SUBSTRING([timeSchedule],2,1) as c2,
        SUBSTRING([timeSchedule],3,1) as c3,
        SUBSTRING([timeSchedule],4,1) as c4,
        SUBSTRING([timeSchedule],5,1) as c5,
        SUBSTRING([timeSchedule],6,1) as c6,
        SUBSTRING([timeSchedule],7,1) as c7,
        SUBSTRING([timeSchedule],8,1) as c8,
        SUBSTRING([timeSchedule],9,1) as c9
    from myTable
)
select 
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c1)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c2)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c3)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c4)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c5)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c6)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c7)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c8)))+
    CONVERT( NVARCHAR(50),MAX(CONVERT(INT,c9)))
from MyCTE

-- MAX char depends on collate (like sorting, comparing)
-- but this solution fit most collate as least, if not all,
-- since "1" bigger than "0"
-- In this solution you need to remember that you will not get the "zero padding"
-- the solution will be in the len of the bigger len
;With MyCTE as (
    select 
        SUBSTRING([timeSchedule],1,1) as c1,
        SUBSTRING([timeSchedule],2,1) as c2,
        SUBSTRING([timeSchedule],3,1) as c3,
        SUBSTRING([timeSchedule],4,1) as c4,
        SUBSTRING([timeSchedule],5,1) as c5,
        SUBSTRING([timeSchedule],6,1) as c6,
        SUBSTRING([timeSchedule],7,1) as c7,
        SUBSTRING([timeSchedule],8,1) as c8,
        SUBSTRING([timeSchedule],9,1) as c9
    from myTable
)
select 
    MAX(c1)+
    MAX(c2)+
    MAX(c3)+
    MAX(c4)+
    MAX(c5)+
    MAX(c6)+
    MAX(c7)+
    MAX(c8)+
    MAX(c9)
from MyCTE