Find first non-matching character between two strings












0















Posting this with solution provided.



The use case could be framed in a number of ways:




  • Return all characters that match at the start of two strings, until they do not match

  • How many characters match between two strings before one character doesn't match?

  • What is the first character that doesn't match between two strings?

  • etc.


For example:



If I have the strings 'Interesting' and 'Interested' they are similar for all the characters 'Interest' and then the first string ends in 'ing' while the second ends in 'ed'. Therefore, they have 8 characters in common, the first non-matching character is the 9th and the identical string is 'Interest'.



For the question in the title, the first non-matching character is number 9.









share



























    0















    Posting this with solution provided.



    The use case could be framed in a number of ways:




    • Return all characters that match at the start of two strings, until they do not match

    • How many characters match between two strings before one character doesn't match?

    • What is the first character that doesn't match between two strings?

    • etc.


    For example:



    If I have the strings 'Interesting' and 'Interested' they are similar for all the characters 'Interest' and then the first string ends in 'ing' while the second ends in 'ed'. Therefore, they have 8 characters in common, the first non-matching character is the 9th and the identical string is 'Interest'.



    For the question in the title, the first non-matching character is number 9.









    share

























      0












      0








      0








      Posting this with solution provided.



      The use case could be framed in a number of ways:




      • Return all characters that match at the start of two strings, until they do not match

      • How many characters match between two strings before one character doesn't match?

      • What is the first character that doesn't match between two strings?

      • etc.


      For example:



      If I have the strings 'Interesting' and 'Interested' they are similar for all the characters 'Interest' and then the first string ends in 'ing' while the second ends in 'ed'. Therefore, they have 8 characters in common, the first non-matching character is the 9th and the identical string is 'Interest'.



      For the question in the title, the first non-matching character is number 9.









      share














      Posting this with solution provided.



      The use case could be framed in a number of ways:




      • Return all characters that match at the start of two strings, until they do not match

      • How many characters match between two strings before one character doesn't match?

      • What is the first character that doesn't match between two strings?

      • etc.


      For example:



      If I have the strings 'Interesting' and 'Interested' they are similar for all the characters 'Interest' and then the first string ends in 'ing' while the second ends in 'ed'. Therefore, they have 8 characters in common, the first non-matching character is the 9th and the identical string is 'Interest'.



      For the question in the title, the first non-matching character is number 9.







      sql-server t-sql





      share












      share










      share



      share










      asked 1 min ago









      youcantryreachingmeyoucantryreachingme

      3437




      3437






















          1 Answer
          1






          active

          oldest

          votes


















          0














          There is a very similar question on another forum in which somebody wants to compare varchar arrays 1 character at a time.



          There are various answers there that include iterating through strings one character at a time, and lengthy scripts that introduce new tables of numbers into your database. And then there is a little gem of an answer that uses bit-wise comparison to detect the first difference between strings.



          To do so, that solution converts each character of each string into binary representation. Due to the limit on the number of numerals permitted within the bigint data type there is the short note that this "is a nice function that works for up to 8 characters!"



          Of course, extending this to support longer strings involves the trivial step of breaking the supplied strings into 8 character chunks - which is the solution I provide here.



          create FUNCTION dbo.fnFirstDifference(@FirstString VARCHAR(256),@SecondString VARCHAR(256))
          RETURNS int
          AS
          BEGIN
          DECLARE @var VARBINARY(8),
          @firstChunk varchar(8),
          @secondChunk varchar(8),
          @chunkStart int,
          @loopCount int,
          @pos TINYINT

          set @chunkStart = 1
          set @loopCount = -1;
          set @pos = 0;

          set @firstChunk = substring(@firstString, @chunkStart, 8);
          set @secondChunk = substring(@secondString, @chunkStart, 8);

          while @pos = 0 and (datalength(@firstChunk) > 0 or datalength(@secondChunk) > 0)
          begin

          SELECT @var = CAST(CAST(@FirstChunk AS BINARY(8)) AS BIGINT) ^ CAST(CAST(@SecondChunk AS BINARY(8)) AS BIGINT),
          @pos = PATINDEX('%[^0]%', SUBSTRING(master.dbo.fn_sqlvarbasetostr(@var), 3, 32)),
          @pos = (1 + @pos) / 2

          set @chunkStart = @chunkStart + 8;
          set @firstChunk = substring(@firstString, @chunkStart, 8);
          set @secondChunk = substring(@secondString, @chunkStart, 8);
          set @loopCount = @loopCount + 1;
          end

          if @pos <> 0 set @pos = (@loopCount * 8) + @pos;

          RETURN @pos
          END


          The original function returned a varchar providing all matching characters, plus the first non-matching character, from string 1, then a hyphen, and then likewise for string 2. For example: 'Interesti-Intereste'. I have converted this to provide the position of the first non-matching character - thus returning 9 for the following example:



           select dbo.fnFIrstDifference('Interesting', 'Interested')


          Adapting to the other use cases is trivial.





          share























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "182"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232817%2ffind-first-non-matching-character-between-two-strings%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            There is a very similar question on another forum in which somebody wants to compare varchar arrays 1 character at a time.



            There are various answers there that include iterating through strings one character at a time, and lengthy scripts that introduce new tables of numbers into your database. And then there is a little gem of an answer that uses bit-wise comparison to detect the first difference between strings.



            To do so, that solution converts each character of each string into binary representation. Due to the limit on the number of numerals permitted within the bigint data type there is the short note that this "is a nice function that works for up to 8 characters!"



            Of course, extending this to support longer strings involves the trivial step of breaking the supplied strings into 8 character chunks - which is the solution I provide here.



            create FUNCTION dbo.fnFirstDifference(@FirstString VARCHAR(256),@SecondString VARCHAR(256))
            RETURNS int
            AS
            BEGIN
            DECLARE @var VARBINARY(8),
            @firstChunk varchar(8),
            @secondChunk varchar(8),
            @chunkStart int,
            @loopCount int,
            @pos TINYINT

            set @chunkStart = 1
            set @loopCount = -1;
            set @pos = 0;

            set @firstChunk = substring(@firstString, @chunkStart, 8);
            set @secondChunk = substring(@secondString, @chunkStart, 8);

            while @pos = 0 and (datalength(@firstChunk) > 0 or datalength(@secondChunk) > 0)
            begin

            SELECT @var = CAST(CAST(@FirstChunk AS BINARY(8)) AS BIGINT) ^ CAST(CAST(@SecondChunk AS BINARY(8)) AS BIGINT),
            @pos = PATINDEX('%[^0]%', SUBSTRING(master.dbo.fn_sqlvarbasetostr(@var), 3, 32)),
            @pos = (1 + @pos) / 2

            set @chunkStart = @chunkStart + 8;
            set @firstChunk = substring(@firstString, @chunkStart, 8);
            set @secondChunk = substring(@secondString, @chunkStart, 8);
            set @loopCount = @loopCount + 1;
            end

            if @pos <> 0 set @pos = (@loopCount * 8) + @pos;

            RETURN @pos
            END


            The original function returned a varchar providing all matching characters, plus the first non-matching character, from string 1, then a hyphen, and then likewise for string 2. For example: 'Interesti-Intereste'. I have converted this to provide the position of the first non-matching character - thus returning 9 for the following example:



             select dbo.fnFIrstDifference('Interesting', 'Interested')


            Adapting to the other use cases is trivial.





            share




























              0














              There is a very similar question on another forum in which somebody wants to compare varchar arrays 1 character at a time.



              There are various answers there that include iterating through strings one character at a time, and lengthy scripts that introduce new tables of numbers into your database. And then there is a little gem of an answer that uses bit-wise comparison to detect the first difference between strings.



              To do so, that solution converts each character of each string into binary representation. Due to the limit on the number of numerals permitted within the bigint data type there is the short note that this "is a nice function that works for up to 8 characters!"



              Of course, extending this to support longer strings involves the trivial step of breaking the supplied strings into 8 character chunks - which is the solution I provide here.



              create FUNCTION dbo.fnFirstDifference(@FirstString VARCHAR(256),@SecondString VARCHAR(256))
              RETURNS int
              AS
              BEGIN
              DECLARE @var VARBINARY(8),
              @firstChunk varchar(8),
              @secondChunk varchar(8),
              @chunkStart int,
              @loopCount int,
              @pos TINYINT

              set @chunkStart = 1
              set @loopCount = -1;
              set @pos = 0;

              set @firstChunk = substring(@firstString, @chunkStart, 8);
              set @secondChunk = substring(@secondString, @chunkStart, 8);

              while @pos = 0 and (datalength(@firstChunk) > 0 or datalength(@secondChunk) > 0)
              begin

              SELECT @var = CAST(CAST(@FirstChunk AS BINARY(8)) AS BIGINT) ^ CAST(CAST(@SecondChunk AS BINARY(8)) AS BIGINT),
              @pos = PATINDEX('%[^0]%', SUBSTRING(master.dbo.fn_sqlvarbasetostr(@var), 3, 32)),
              @pos = (1 + @pos) / 2

              set @chunkStart = @chunkStart + 8;
              set @firstChunk = substring(@firstString, @chunkStart, 8);
              set @secondChunk = substring(@secondString, @chunkStart, 8);
              set @loopCount = @loopCount + 1;
              end

              if @pos <> 0 set @pos = (@loopCount * 8) + @pos;

              RETURN @pos
              END


              The original function returned a varchar providing all matching characters, plus the first non-matching character, from string 1, then a hyphen, and then likewise for string 2. For example: 'Interesti-Intereste'. I have converted this to provide the position of the first non-matching character - thus returning 9 for the following example:



               select dbo.fnFIrstDifference('Interesting', 'Interested')


              Adapting to the other use cases is trivial.





              share


























                0












                0








                0







                There is a very similar question on another forum in which somebody wants to compare varchar arrays 1 character at a time.



                There are various answers there that include iterating through strings one character at a time, and lengthy scripts that introduce new tables of numbers into your database. And then there is a little gem of an answer that uses bit-wise comparison to detect the first difference between strings.



                To do so, that solution converts each character of each string into binary representation. Due to the limit on the number of numerals permitted within the bigint data type there is the short note that this "is a nice function that works for up to 8 characters!"



                Of course, extending this to support longer strings involves the trivial step of breaking the supplied strings into 8 character chunks - which is the solution I provide here.



                create FUNCTION dbo.fnFirstDifference(@FirstString VARCHAR(256),@SecondString VARCHAR(256))
                RETURNS int
                AS
                BEGIN
                DECLARE @var VARBINARY(8),
                @firstChunk varchar(8),
                @secondChunk varchar(8),
                @chunkStart int,
                @loopCount int,
                @pos TINYINT

                set @chunkStart = 1
                set @loopCount = -1;
                set @pos = 0;

                set @firstChunk = substring(@firstString, @chunkStart, 8);
                set @secondChunk = substring(@secondString, @chunkStart, 8);

                while @pos = 0 and (datalength(@firstChunk) > 0 or datalength(@secondChunk) > 0)
                begin

                SELECT @var = CAST(CAST(@FirstChunk AS BINARY(8)) AS BIGINT) ^ CAST(CAST(@SecondChunk AS BINARY(8)) AS BIGINT),
                @pos = PATINDEX('%[^0]%', SUBSTRING(master.dbo.fn_sqlvarbasetostr(@var), 3, 32)),
                @pos = (1 + @pos) / 2

                set @chunkStart = @chunkStart + 8;
                set @firstChunk = substring(@firstString, @chunkStart, 8);
                set @secondChunk = substring(@secondString, @chunkStart, 8);
                set @loopCount = @loopCount + 1;
                end

                if @pos <> 0 set @pos = (@loopCount * 8) + @pos;

                RETURN @pos
                END


                The original function returned a varchar providing all matching characters, plus the first non-matching character, from string 1, then a hyphen, and then likewise for string 2. For example: 'Interesti-Intereste'. I have converted this to provide the position of the first non-matching character - thus returning 9 for the following example:



                 select dbo.fnFIrstDifference('Interesting', 'Interested')


                Adapting to the other use cases is trivial.





                share













                There is a very similar question on another forum in which somebody wants to compare varchar arrays 1 character at a time.



                There are various answers there that include iterating through strings one character at a time, and lengthy scripts that introduce new tables of numbers into your database. And then there is a little gem of an answer that uses bit-wise comparison to detect the first difference between strings.



                To do so, that solution converts each character of each string into binary representation. Due to the limit on the number of numerals permitted within the bigint data type there is the short note that this "is a nice function that works for up to 8 characters!"



                Of course, extending this to support longer strings involves the trivial step of breaking the supplied strings into 8 character chunks - which is the solution I provide here.



                create FUNCTION dbo.fnFirstDifference(@FirstString VARCHAR(256),@SecondString VARCHAR(256))
                RETURNS int
                AS
                BEGIN
                DECLARE @var VARBINARY(8),
                @firstChunk varchar(8),
                @secondChunk varchar(8),
                @chunkStart int,
                @loopCount int,
                @pos TINYINT

                set @chunkStart = 1
                set @loopCount = -1;
                set @pos = 0;

                set @firstChunk = substring(@firstString, @chunkStart, 8);
                set @secondChunk = substring(@secondString, @chunkStart, 8);

                while @pos = 0 and (datalength(@firstChunk) > 0 or datalength(@secondChunk) > 0)
                begin

                SELECT @var = CAST(CAST(@FirstChunk AS BINARY(8)) AS BIGINT) ^ CAST(CAST(@SecondChunk AS BINARY(8)) AS BIGINT),
                @pos = PATINDEX('%[^0]%', SUBSTRING(master.dbo.fn_sqlvarbasetostr(@var), 3, 32)),
                @pos = (1 + @pos) / 2

                set @chunkStart = @chunkStart + 8;
                set @firstChunk = substring(@firstString, @chunkStart, 8);
                set @secondChunk = substring(@secondString, @chunkStart, 8);
                set @loopCount = @loopCount + 1;
                end

                if @pos <> 0 set @pos = (@loopCount * 8) + @pos;

                RETURN @pos
                END


                The original function returned a varchar providing all matching characters, plus the first non-matching character, from string 1, then a hyphen, and then likewise for string 2. For example: 'Interesti-Intereste'. I have converted this to provide the position of the first non-matching character - thus returning 9 for the following example:



                 select dbo.fnFIrstDifference('Interesting', 'Interested')


                Adapting to the other use cases is trivial.






                share











                share


                share










                answered 1 min ago









                youcantryreachingmeyoucantryreachingme

                3437




                3437






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232817%2ffind-first-non-matching-character-between-two-strings%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Liste der Baudenkmale in Friedland (Mecklenburg)

                    Single-Malt-Whisky

                    Czorneboh