How to perform boolean addition to SQL select
I have the following data in a column of a database table named timeSchedule
00100110 00010100 00110000 00110011
Boolean addition will 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’);
source to share
OK, Now we have the DDL (unfortunately no DML, but only one line). we can provide a solution :-)
Firstly! I highly recommend DON'T USE the solution above, NO NEED for loops , even if you are not using a fixed data length, we know the maximum length (50).
Secondly! If you are going to parse text, you should use SQLCLR and not loop and parsing using T-SQL, in most cases like this one.
Third :-) here is a simple example for a simple solution. I only used the first 10 characters ... you can continue up to 50 ... you can use a dynamic query to create a query if you don't want to write it yourself by hand (there are other solutions too, I recommend checking the execution plan and IO used to choose 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
source to share
The first thing you need is a way to get the string and convert it to #. 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 this in another function to convert it back to string representation. This is a good example.
By the way, storing binary code as a string is almost always the wrong approach.
source to share
You can use the following query to perform a bit-wise OR of each character 1
, 0
contained in a field [timeSchedule]
in 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 table of tables to explode each character in the column [timeSchedule]
. Then use MAX
to perform a bitwise OR on the bit position. Finally, use FOR XML PATH
to concatenate all individual bits into a string.
Note. This query will work even for variable length values [timeSchedule]
, that is, for any value contained in a column with a length between 1 and 50.
source to share
Here are two more solutions :-) the logic is the same. But as soon as I saw my solution in practice, I realized that I didn't need to use SUM, as we just need to select MAX. Further, since CHAR 1 is greater than CHAR 0 (char, not number) in most collations (cultures), we also don't need CONVERT, and we can just select MAX from CHAR. so here are 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
source to share