Find Unmatching Letters between Two Strings

Question

I'm trying to write a query that gives total numbers of unmatched letters between two strings.

For example, I have the given two strings

String 1: Jamess String 2: Romeeo

I need to find out total number of letters in the second string that don't having a matching in the first string.

The letters would be

R, o, o and e

(note that the first string has only one e, so the extra e in Romeeo doesn't having a matching in string 1).

In short, Those letters (R, o, o, and e) don't exist in string 1.

Is there away to solve this problem in Oracle SQL?

Answer 1

The question is tagged with [plsql] so I thought a PL/SQL solution would be in order:

DECLARE 
  stringA  VARCHAR2(20) := 'Jamess';
  stringB  VARCHAR2(20) := 'Romeeo';
  strDiff  VARCHAR2(20);

  FUNCTION find_unmatched(p1 IN VARCHAR2, p2 IN VARCHAR2)
    RETURN VARCHAR2
  IS
    s1 VARCHAR2(32767) := p1;
    s2 VARCHAR2(32767) := p2;
    s3 VARCHAR2(32767);
    c   CHAR(1);
    p   NUMBER;
  BEGIN
    LOOP
      c := SUBSTR(s2, 1, 1);

      p := INSTR(s1, c);
      IF p = 0 THEN  -- c not found in s1: add to unmatched list and remove from s2
        s3 := s3 || c;
        s2 := SUBSTR(s2, 2);
      ELSE  -- c found in s1: remove from s1 and s2
        s1 := SUBSTR(s1, 1, p-1) || SUBSTR(s1, p+1, LENGTH(s1)-p);
        s2 := SUBSTR(s2, 2);
      END IF;

      IF s1 IS NULL OR s2 IS NULL THEN
        EXIT;
      END IF;
    END LOOP;

    RETURN s3;
  END find_unmatched;

BEGIN
  strDiff := find_unmatched(stringA, stringB);

  DBMS_OUTPUT.PUT_LINE('strDiff=''' || strDiff || '''');
END;

Share and enjoy.

Answer 2

Amusing puzzle game ;)

Using the analytic function COUNT() and by partitioning up to the current row, you are in fact able to "number yours letters":

  SELECT letters, 
         COUNT(*) OVER (PARTITION BY letters ORDER BY n ROWS 
                        BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cnt FROM (
    --                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    SELECT SUBSTR('Jameess', LEVEL, 1) letters, LEVEL n FROM DUAL
    CONNECT BY LEVEL <= LENGTH('Jameess')
  )

Producing that result:

LETTERS CNT
J       1   -- first J
a       1   -- first a
e       1   -- first e
e       2   -- second e
m       1   -- ...
s       1
s       2

Do it once for each string, and you only have to compare each letter index it its own group:

SELECT s2.letters
FROM (
  SELECT letters,
         COUNT(*) OVER (PARTITION BY letters ORDER BY n ROWS 
                        BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cnt FROM (
    SELECT SUBSTR('Jameess', LEVEL, 1) letters, LEVEL n FROM DUAL
    CONNECT BY LEVEL <= LENGTH('Jameess')
  )
) S1
RIGHT OUTER JOIN (
  SELECT letters, 
         COUNT(*) OVER (PARTITION BY letters ORDER BY n ROWS 
                        BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cnt FROM (
    SELECT SUBSTR('Romeeeeo', LEVEL, 1) letters, LEVEL n FROM DUAL
    CONNECT BY LEVEL <= LENGTH('Romeeeeo')
  )
) S2
ON s1.letters = s2.letters AND s1.cnt = s2.cnt
WHERE s1.cnt IS NULL
--    ^^^^^^
-- change to `s2.cnt` to compare your strings the other way around 
-- and replace the RIGHT JOIN by a LEFT JOIN
ORDER BY letters

Producing:

LETTERS
R
e
e
o
o

_{(For testing purposes, I add few extra e in Jameess and Romeeeeo)}

Answer 3

In Oracle,

SQL> WITH DATA AS
  2    ( SELECT 'Jamess' str1, 'Romeeo' str2 FROM dual
  3    ),
  4    data2 AS
  5    (SELECT SUBSTR(str1, LEVEL, 1) str1
  6    FROM DATA
  7      CONNECT BY LEVEL <= LENGTH(str1)
  8    ),
  9    data3 AS
 10    (SELECT SUBSTR(str2, LEVEL, 1) str2
 11    FROM DATA
 12      CONNECT BY LEVEL <= LENGTH(str2)
 13    )
 14  SELECT * FROM data3 WHERE str2 NOT IN
 15    (SELECT str1 FROM data2
 16    )
 17  UNION ALL
 18  SELECT str2
 19  FROM data3
 20  WHERE str2 IN
 21    (SELECT str1 FROM data2
 22    )
 23  GROUP BY str2
 24  HAVING COUNT(*)>1
 25  /

S
-
R
o
o
e

SQL>

Answer 4

Try this SQL Query

DECLARE @string1 VARCHAR(100)='Jamess'
DECLARE @string2 VARCHAR(100)='Romeeo'
DECLARE @Notmatchstring VARCHAR(100)
SET @Notmatchstring =''
DECLARE @index INT
DECLARE @count INT

SET @count=1
WHILE @count <= LEN(@string2)
BEGIN

    SET @index=CHARINDEX(SUBSTRING(@string2,@count,1),@string1,0)   

    IF(@index=0)
    BEGIN
    SET @Notmatchstring =@Notmatchstring +' '+SUBSTRING(@string2,@count,1)
    END
    IF(@index>0)
    BEGIN   
    SET @string1=REPLACE(@string1,SUBSTRING(@string2,@count,1),'')
    END
    SET @count=@count+1
END
SELECT @Notmatchstring as NotMatchingCharacter