compare comma seperated strings if there is the same number in them

Question

I have two strings the first one looks like this: 24,36,78,98 the second one like this: 25,36,88,98

Now what i want to do is to compare the two strings in sql and only return the numbers that are the same in each string.

Is this possible in sql or do i have to write my own function in like PL/SQL?

I googled but could not find any answers to this question.

Answer 1

This can be done in sql or pl/sql depending on your needs:

plsql

If you have Oracle APEX installed in your db you have the APEX_STRING api that you can use to convert the string to an array. Then use the MULTISET operator to get the intersect of the values:

set serveroutput on size 999999
clear screen
DECLARE
  l_string1 VARCHAR2(100) :=  '24,36,78,98';
  l_string2 VARCHAR2(100) :=  '25,36,88,98';
  l_string_intersect VARCHAR2(100);
  l_string1_arr apex_t_varchar2;
  l_string2_arr apex_t_varchar2;
  l_intersect_arr  apex_t_varchar2;
BEGIN 
  l_string1_arr :=  apex_string.split(l_string1,',');
  l_string2_arr :=  apex_string.split(l_string2,',');
  l_intersect_arr := l_string1_arr MULTISET INTERSECT l_string2_arr;
  l_string_intersect := apex_string.join(l_intersect_arr,',');
  dbms_output.put_line('intersect values: '||l_string_intersect);
END;
/

PL/SQL procedure successfully completed.



intersect values: 36,98

Without APEX and if you still want pl/sql, then use a sql technique to split (see below) and use the MULTISET operator to get the intersect.

sql

for sql, have a look at this blog on the various methods to split a comma separated string into values.

Here is an example of how to do it using a technique described in the blog above:

with 
string1 as (
  select '24,36,78,98' str from dual
),
string2 as (
  select '25,36,88,98' str from dual
), 
string1_tab as (
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   string1
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) ) + 1
),
string2_tab as (
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   string2
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) ) + 1
)
select * from string1_tab
INTERSECT
select * from string2_tab
;