Best way for OR 2 lines 0.1

Question

I have 2 lines that contain only 0 and 1. I want the result line to beat OR their charcter by character.

DECLARE @str1 nvarchar; DECLARE @str2 nvarchar; SET @str1= '11001100'; SET @str2= '00100110'; -- I want result to be : 11101110 

Row size is variable. I can use the for and OR loop characters one by one. but the number of lines is variable and their size can be more than 1 million ... Is there a better way than a FOR loop?

Answer 1

Ideally, you will encode this as binary.

11001100 is a single byte of 0xCC .

Saving as varchar means that it takes 8 bytes and declared as nvarchar , it takes 16 bytes.

Then you can also use the CLR and bitwise operators.

The answer to your question, although using the CLR function, is probably still the most efficient way.

 using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [SqlFunction] public static SqlString StringOr(SqlChars a, SqlChars b) { if (a.Length != b.Length) { throw new Exception("Strings should be the same length."); } char[] c = new char[a.Length]; for(int i =0; i < a.Length; i++) { c[i] = (a[i] == '0' && b[i] == '0' ? '0' : '1'); } return (SqlString)(new SqlChars(c)); } } 

Answer 2

The loop-free option is a purely set-based approach (with a recursive CTE) and therefore should be quite efficient compared to any type of loop. You can use this function to connect or apply it to other data sets (tables or views)

 -- function to split binary string to result-set alter function [dbo].[SplitStringToResultSet] (@value varchar(max), @size int) returns table as return with r as ( select right(value, 1) [bit] , left(value, len(value)-1) [value] , 0 [pos] from (select rtrim(cast( case when len(@value) > @size then left(@value, @size) when len(@value) < @size then @value + replicate('0', @size - len(@value)) else @value end as varchar(max))) [value]) as j union all select right(value, 1) , left(value, len(value)-1) , pos + 1 from r where value > '') select cast([bit] as int) [bit], [pos] from r -- usage ------------------------------------------------- declare @OR varchar(20) = '', @AND varchar(20) = ''; select @OR = @OR + cast(n1.[bit] | n2.[bit] as varchar(1)) , @AND = @AND + cast(n1.[bit] & n2.[bit] as varchar(1)) -- XOR etc from [dbo].[SplitStringToResultSet] ('11001100', 8) n1 full join [dbo].[SplitStringToResultSet] ('00100110', 8) as n2 on n1.[pos] = n2.[pos] order by n1.pos desc select @OR [OR], @AND [AND] 

Result

 OR AND -------------------- 11101110 00000100 

Answer 3

Try below

 DECLARE @str1 nvarchar(10); DECLARE @str2 nvarchar(10); DECLARE @result nvarchar(10) = ''; declare @counter1 as int = 1; SET @str1= '11001100'; SET @str2= '00100110'; while @counter1 <= len(@str1) begin if (cast(substring(@str1,@counter1,1) as int) + cast(substring(@str2,@counter1,1) as int) >= 1) set @result += '1' else set @result += '0' set @counter1 += 1 end print @result 

Answer 4

Very cool question and solutions. I add one more using xml: -convert both lines to xml, where each char is a node-calculate their table with bit and ordinal -join ordinal extra bits

 DECLARE @str1 nvarchar(max) DECLARE @str2 nvarchar(max) declare @s1xml xml declare @s2xml xml SET @str1= '11001100' SET @str2= '00100110' set @s1xml =(select cast(replace(replace(@str1,'1','<n>1</n>'),'0','<n>0</n>') as xml)) set @s2xml =(select cast(replace(replace(@str2,'1','<n>1</n>'),'0','<n>0</n>') as xml)) select case when a.bit+b.bit = 0 then 0 else 1 end from (select n.value('.','int') bit, n.value('for $i in . return count(../*[. << $i]) + 1', 'int') position from @s1xml.nodes('//n') as T1(n)) a join (select n.value('.','int') bit, n.value('for $i in . return count(../*[. << $i]) + 1', 'int') position from @s2xml.nodes('//n') as T2(n)) b ON a.position=b.position for xml path('') 

Answer 5

Assuming the maximum row length is not equal to a million, but much less, I would use a lookup table with two columns and 2 ^ max row length rows with char strings and corresponding binary values. You can then append two rows to two instances of the lookup table and use the bitwise OR functions for the results.

 select bitstr1, bitstr2, b1.bin, b2.bin, b1.bin | b2.bin as OR_result from tblMillion inner join tblLkpBin b1 on bitstr1 = b1.str inner join tblLkpBin b2 on bitstr2 = b2.str