Comparing two different strings in SQL that are in a different order

This is an awful, horrible way to store lists. But, Oracle has this amazing thing called regular expressions and they will allow you to do this. To get a refresher on them, try running this:

with t as (
      select '((a,)|(c,)|(b,)){3}' as pat from dual
     )
select pat, (case when regexp_like('a,b,c' ||',', pat) then 1 else 0 end)
from t;

This returns true -- leading to the conclusion that we can bastardize a string to turn it into a regular expression to get a match on elements in another string. For full equality, we want to do the comparison both ways.

So, here is some code that seems to solve your problem:

with t as (
      select 'ABC,DEF,XYZ' as val1, 'ABC,XYZ,DEF' as val2 from dual union all
      select 'ABC,DEF,XYZ', 'ABC,XYZ,LMN' from dual
     )
select t.*,
       (case when regexp_like(val1 || ',', pat2||'{'||val2_len||'}') and
                  regexp_like(val2 || ',', pat1||'{'||val1_len||'}')
             then 1 else 0
        end) as comp 
from (select t.*,
             replace('((' || replace(val1 || ',', ',', ',)|(')||'))', '|())', ')') as pat1,
             replace('((' || replace(val2 || ',', ',', ',)|(')||'))', '|())', ')') as pat2,
             length(val1) - length(replace(val1, ',', '')) + 1 as val1_len,
             length(val2) - length(replace(val2, ',', '')) + 1 as val2_len
      from t
     ) t ;

This might not work if you have repeating values in the list. But, I will repeat, using junction tables or nested tables is the right way to store this information. Not string-encoded lists.

1. SPLIT EACH STRING BY USING DELIMINATOR (,) INTO 3 SHORT STRING LIKE 'ABC' 'DEF' 'XYZ'
2. STORE THESE 3 SHORT STRING IN DIFFERENT TABLE.
3. USE SOMETHING LIKE

SELECT *

FROM (SELECT * FROM TABLE1

   EXCEPT

  SELECT * FROM TABL2)

IF THE RESULT IS NULL THEN 'EQUAL'

USE THIS FUNCTION TO SPLIT

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END