Compares strings if containing sub-strings whatever the order

Question

Let's say we have a string such as

AABBCCDDEEFFGG

It contains 7 sub-strings

AA BB CC DD EE FF GG

Now let's reorganize the order as long as the new string contains the sub-strings, then we think that they are equal. The new string is

AACCFFGGEEBBDD

It just was changed the order as

AA CC FF GG EE BB DD

I have many combinations from sub-strings. Each sub-string has exactly two chars. How can I compare the long string as same in stored procedure? Let say the old string is from database, the new one is from input parameter.

How do you define "substring"? Is it just two consecutive characters? — eaolson
– eaolson, Commented Mar 21, 2019 at 1:01

Kaushik Nayak · Accepted Answer · 2019-03-21 04:22:40Z

1

You may create a function which splits the strings and compares the strings.

CREATE OR REPLACE FUNCTION compare_str (
     p_str1 VARCHAR2,
     p_str2 VARCHAR2
) RETURN INTEGER AS
     TYPE strytype IS
          TABLE OF VARCHAR2(100);
     str_t1   strytype;
     str_t2   strytype;
BEGIN
     SELECT substr(p_str1, (level - 1) * 2 + 1,2) AS ch BULK COLLECT
        INTO str_t1
     FROM dual CONNECT BY
          level <= length(p_str1) / 2
     ORDER BY ch;

     SELECT substr(p_str2, (level - 1) * 2 + 1,2) AS ch BULK COLLECT
     INTO str_t2
     FROM dual CONNECT BY
          level <= length(p_str2) / 2
     ORDER BY ch;

     IF str_t1 = str_t2
     THEN
          RETURN 1;
     ELSE
          RETURN 0;
     END IF;
END;
/

So, In your queries or procedures, you may simply call the function passing the appropriate columns / strings as arguments.

select compare_str('AABBCCDDEEFFGG','AACCFFGGEEBBDD') from dual;

1

DEMO

answered Mar 21, 2019 at 4:22

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bigeyes Over a year ago

Thank you so much.

Barbaros Özhan · Accepted Answer · 2019-03-21 05:44:27Z

You can use the function below

create or replace function str_comparison ( i_String1 varchar2, i_String2 varchar2 )
                     return pls_integer is
  o_result pls_integer;
begin
  with t(str1,str2) as
  (
   select i_String1, i_String2 from dual
  ), t2(s_str1,s_str2) as
  (
  select substr(str1,-2*level,2),
         substr(str2,-2*level,2)
    from t
   connect by level <= length(str1)/2
  ), t3 as
  (
  select listagg(s_str1) within group ( order by s_str1 )
         as str1,
         listagg(s_str2) within group ( order by s_str2 )
         as str2
    from t2
  )
  select decode(str1,str2,1,0)
    into o_result
    from t3;

  return o_result;
end;

and see substitution AACCFFGGEEBBDD and AABBCCDDEEFFGG for the arguments yield 1 showing those strings are identical whenever ordered due to your logic, otherwise you get 0 (zero).

Collectives™ on Stack Overflow

Compares strings if containing sub-strings whatever the order

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related