Best way to OR 2 strings of 0,1

Question

I have 2 string that just contains 0 and 1. I want a result string that bitwise OR them charcter by character.

DECLARE @str1 nvarchar;
DECLARE @str2 nvarchar; 
SET @str1= '11001100';
SET @str2= '00100110';

-- I want result to be : 11101110

The size of string is variable. I can use a for loop and OR the characters one by one. but the number of strings is variable and the size of them may be more than 1 million... Is there any better way than FOR loop?

Answer 1

Ideally you would be encoding this as binary.

11001100 is a single byte 0xCC.

Storing as varchar means it takes 8 bytes and declared as nvarchar it takes 16 bytes.

You could then also use CLR and bitwise operators.

Answering the question you asked though using a CLR function would likely still be by far the best performing 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));        
    }
}