Compare two varchar2 partially

user_10341896 0
Viewed 73

I have to compare two Varchar2 in PL/SQL, but in blocks of 3 characters. For example, the following two strings: "Synchronize" and "front" would match because both contain the substring "ron".

To solve this issue, I could get all blocks of three consecutive characters of the first string and look for any of them in the second string.

My question: Is there an easier/optimize solution to get the same result in PL/SQL?

Thanks in advance.

Below, you can find a sample of code in which v_found would contain a value greater than 0 if there is a match:

DECLARE  
    v_string1   VARCHAR2(30) := '1234567';  
    v_string2    VARCHAR2(30) := 'abcd345efg';  
    v_substring    VARCHAR2 (100);  
  v_len          PLS_INTEGER;  
  v_found        PLS_INTEGER;  
BEGIN  
  v_len := LENGTH(v_string1);  
  IF (v_len > 2 ) THEN  
    FOR i IN 1 .. v_len - 2  
    LOOP  
      v_substring := SUBSTR( v_string1, i, 3 );  
      dbms_output.put_line( v_substring );  
      v_found := INSTR( v_string2, v_substring );  
      dbms_output.put_line( 'v_string2<' || v_string2 || '>, v_substring<' || v_substring || '>, v_found <' || v_found || '>' );  
    END LOOP;  
  END IF;  
END;
compare substring plsql string
1 Answers

Say you have a string and you need to get all the substrings of 3 characters; this could be a way:

select substr(string1, level, 3)
from (select '1234567' string1 from dual)
connect by level +2 <= length(string1);

SUBSTR(STRING1,LEVEL,3)         
--------------------------------
123                             
234                             
345                             
456                             
567

You can use this to get all the substrings from both strings and then make the check with a JOIN:

DECLARE  
    v_string1   VARCHAR2(30) := '1234567';  
    v_string2   VARCHAR2(30) := 'abcd345efg'; 
    v_check     number;
BEGIN    
    select count(*)
    into v_check
    from (
            select substr(v_string1, level, 3) token
            from dual
            connect by level +2 <= length(v_string1)
         ) tokens_1
         inner join 
         (
            select substr(v_string2, level, 3) token
            from dual
            connect by level +2 <= length(v_string2)
         ) tokens_2
         on (tokens_1.token = tokens_2.token);
    if v_check = 1 then
        dbms_output.put_line('MATCH');
    else
        dbms_output.put_line('NO MATCH');
    end if;
END;

A more compact way could be:

select case when max(instr(string2, substr(string1, level, 3))) = 0
        then 'NO MATCH'
        else 'MATCH'
       end 
from (select '1234567' string1, 'abcd345efg' string2 from dual)
connect by level +2 <= length(string1)
user_57264670
Related