how to compare 2 comma seperated strings in oracle sql

Can I assume by the question you want to check the individual values not just the strings as a whole?

So if string1 is 'vishal,jaiswal' and string 2 is 'jaiswal,vishal' you want them to match?

Assuming you want identical elements, regardless of order to match then try using str2tbl and then compare the resulting sets

SELECT CASE
           WHEN str2tbl('vishal,jaiswal') = str2tbl('jaiswal,vishal')
           THEN
               'match'
           ELSE
               'no match'
       END
  FROM DUAL;

SELECT CASE
           WHEN str2tbl('vishal,jaiswal') = str2tbl('vishal,kumar')
           THEN
               'match'
           ELSE
               'no match'
       END
  FROM DUAL;

Select allOpen in new window

There are many versions of str2tbl on the internet.
Here's one I use frequently...

CREATE OR REPLACE TYPE vctab as table of varchar2(4000);

CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
    RETURN vctab
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE (v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW (SUBSTR(p_string, v_start));

            v_start := v_length + LENGTH(p_delimiter);
        ELSE
            PIPE ROW (SUBSTR(p_string, v_start, v_index - v_start));

            v_start := v_index + LENGTH(p_delimiter);
        END IF;
    END LOOP;

    RETURN;
END str2tbl;

Select allOpen in new window

If you want exact matches, then just compare the strings directly..

case when a=b then 'match' else 'no match' end

If you have APEX installed, you can use APEX_STRING built-ins for this:

select case
         when apex_string.split('vishal,jaiswal', ',') = apex_string.split('vishal,kumar', ',') then
          'match'
         else
          'no match'
       end is_match
  from dual;

Select allOpen in new window

Maybe this one could also work (no row would mean match, else no match):

-- no match
select *
  from apex_string.split('vishal,jaiswal', ',')
minus
select *
  from apex_string.split('vishal,kumar', ',');

-- match
select *
  from apex_string.split('vishal,jaiswal', ',')
minus
select *
  from apex_string.split('jaiswal,vishal', ',');

Select allOpen in new window

@slightwv (䄆 Netminder) 

I am using listagg and get the string in order. so i am expecting that i will get 'vishal,jaiswal', these 2 values always in order. now i have to compare.

suppose one has 'vishal,kumar,jaiswal' and other has 'vishal,jaiswal' so its a mismatch.

I will take care of order using listagg, but if you have a solution then also it is ok

Then as Sean posted, just compare the strings:
case when a=b then 'match' else 'no match' end

you can't do the MINUS method as Alex showed above without doing it twice  a minus b and then b minus a and confirming both return no rows.
The apex solution is another one of the many str2tbl variations I mentioned.
And as I mentioned before, if you just want to compare two strings of text, then just compare them directly.
The fact that the text is a comma delimited isn't relevant.

@Sean Stuber  I do not want to create a type. we are not allowed to do in production. Function is ok, because i can use in WITh FUNCTION. 

Please advise something where no need to create a type :-( 

slightwv (䄆 Netminder)
Can not create type. its production. I am ok with function. because can use in direct sql as WITH FUNCTION

you don't need a function or a type, if you just want to compare the text of the list to see if they are the same or different just use "="  
if that doesn't work, please give an example that shows the problem and what you expect the results to be

>>Can not create type. its production.

Yes, as we've been saying...
As easy as:

case when 'vishal,jaiswal' = 'vishal,kumar' then 'match' else 'no match' end

I am doing 

listagg(unique aid_category,',') within group(order by eligibility_id) as current_aid_cat

This returns aid_cat_47, aid_cat_17,aid_cat_AL

My other column is :

listagg(unique prev_aid_category,',') within group(order by eligibility_id) as prev_aid_cat

This returns aid_cat_47, aid_cat_57,aid_cat_AL

In this one aid_category is change. I just want to say that there is a mismatch. Because i am comparing all aid_cat they receive previously and now. if both are different then mismatch

doing case in CTE next block like 

case when current_aid_cat = prev_aid_cat then 'match' else 'mismatch' end as is_same gives me error : INVALID NUMBER

With N1 As

(

   Select Up.Id As User_Profile_Id, E.Id As Elg_Id, E.Oep_Id As Oep_Id, E.Determination_Date As Elg_Determination_Date, E.Eligibility_Status

   From Eligibility E, User_Profile Up

   Where Up.Deleted                = 0

   And Up.Id                       = E.User_Profile_Id

   And Up.Id = 959093401

   And E.Eligibility_Status       In ('DETERMINED_VERIFIED','DETERMINED_UNVERIFIED','MARK_AS_OBSOLETE_VERIFIED','MARK_AS_OBSOLETE_UNVERIFIED')

   And E.Oep_Id =(Select Max(E1.Oep_Id) From Eligibility E1 Where E1.User_Profile_Id   = E.User_Profile_Id

               And E1.Eligibility_Status In ('DETERMINED_VERIFIED','DETERMINED_UNVERIFIED','MARK_AS_OBSOLETE_VERIFIED','MARK_AS_OBSOLETE_UNVERIFIED'))

   And E.Determination_Date =(Select Max(E2.Determination_Date) From Eligibility E2 Where E2.User_Profile_Id   = E.User_Profile_Id 

                     And E2.Eligibility_Status In ('DETERMINED_VERIFIED','DETERMINED_UNVERIFIED','MARK_AS_OBSOLETE_VERIFIED','MARK_AS_OBSOLETE_UNVERIFIED')

                     And E2.Oep_Id = E.Oep_Id)

),

N2 As (

   Select N1.*, M1.Id As Elg_Member_Id, Prog.Aid_Category, M1.First_Name, M1.Last_Name, M1.Member_Reference_Id,  E4.Id Previous_Eligibility 

   From N1 N1, Elg_Member M1, Elg_Member_Program_Result Prog, Eligibility E4

   Where N1.Elg_Id=M1.Eligibility_Id

   And M1.Id = Prog.Elg_Member_Id

   And Prog.Aid_Category Is Not Null

   And N1.User_Profile_Id = E4.User_Profile_Id

   And E4.Id = (Select Max(E5.Id) From Eligibility E5 Where E5.User_Profile_Id = E4.User_Profile_Id

            And E5.Eligibility_Status In ('DETERMINED_VERIFIED', 'DETERMINED_UNVERIFIED', 'MARK_AS_OBSOLETE_VERIFIED', 'MARK_AS_OBSOLETE_UNVERIFIED','OBSOLETE')

            And E5.Determination_Date < N1.Elg_Determination_Date

            )

   And 1 < (Select Count(*) From Elg_Member M2 Where M1.Eligibility_Id = M2.Eligibility_Id)

), N3 As( Select N2.User_Profile_Id, Elg_Id As Latest_Eligibility, Previous_Eligibility, Listagg(unique Aid_Category,',') Within Group (Order By Member_Reference_Id) Current_Aid_Cat From N2

Group By N2.User_Profile_Id, Elg_Id , Previous_Eligibility

) ,

N4 As (

Select N2.User_Profile_Id, Elg_Id As Latest_Eligibility, Previous_Eligibility, Listagg(unique Empr4.Aid_Category,',') Within Group (Order By Em4.Member_Reference_Id) Previous_Aid_Cat 

From N2, Elg_Member Em4, Elg_Member_Program_Result Empr4

Where N2.Previous_Eligibility = Em4.Eligibility_Id 

And Em4.Id = Empr4.Elg_Member_Id 

And Empr4.Aid_Category Is Not Null

Group By N2.User_Profile_Id, Elg_Id , Previous_Eligibility

) Select N3.*, N4.Previous_Aid_Cat,N3.Current_Aid_Cat, N4.Previous_Eligibility, Case when N3.Current_Aid_Cat = N4.Previous_Eligibility then 'Match' else 'NO Match' end as Is_same

 From N3, N4

 Where N3.User_profile_id = N4.User_Profile_id;

=====================================

• N1 block is ok.. it gives me latest application for each profile (no need to check.. its perfect)
• N2 block is ok.. It gives me previous application for same profile (no need to check, its perfect)
• N3 block, i used listagg to make a comma seperated string for all aid_category of current application. this gives me current data in comma seperated string
• N4 block, i used listagg again to make a comma seperated string for all aid_category of previous application . this gives me previous data in comma seperated string
• Now i want to check if both form the same string or not. so i used 
• Case when N3.Current_Aid_Cat = N4.Previous_Eligibility then 'Match' else 'NO Match' end as Is_same
  

And this gives error as invalid number. both strings are printed correctly 

slightwv (䄆 Netminder) and Sean Stuber 

Thanks.. you both are correct. I did mistake.. its copy paste, in case i am comparing aid_cat with eligibility.. its straight forward standard error. I did mistake.. its solved.. thanks. thanks you both. you guyz are genius