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

Question

I have two columns in two different tables and want to compare the string values. For example:

TABLE1.COLUMNA value = ABC, DEF, XYZ
TABLE2.COLUMNB value = ABC, XYZ, DEF

I want to return results when the two string are not equal. In this case, I don't care about the order all I care about is that the two values are equal. So the above example the two strings are equal.

These two strings would not be equal

TABLE1.COLUMNA value = ABC, DEF, XYZ
TABLE2.COLUMNB value = ABC, XYZ, LMN

FYI...I am putting this query into a tool that only supports SQL queries only.

Any help would be appreciated.

Tough to do this in a single query, and exactly the reason why you shouldn't store comma separated values in a single field. The core problem is splitting the string, which is hard enough by itself. See [http://stackoverflow.com/questions/3710589/is-there-a-function-to-split-a-string-in-plsql](http://stackoverflow.com/questions/3710589/is-there-a-function-to-split-a-string-in-plsql). — GolezTrol, Nov 07 '14 at 20:40
Well, this is what happens when you use strings to store lists of things. SQL has this great data structure for lists, it is called a "table". Oracle even has nested tables -- forever banishing the merest thought of storing things in a list from anyone who uses the database. Learn about junction tables. — Gordon Linoff, Nov 07 '14 at 20:41
Can you write stored procedures or functions? Then you could 1) Parse each column value 2) Store each value in a new temp table. Comparing the temp tables with a join (so ABC joins ABC etc) would give you any differences between the tables ie the columns of delimited values — Grantly, Nov 07 '14 at 20:42
I guess I could write a stored procedure and call that. That might be my best option. Thanks. — user2786756, Nov 07 '14 at 20:55
How about calculating a checksum on the values and comparing the reuslts. Would that be possible? — user2786756, Nov 07 '14 at 21:20
you can do 'bubble sort' for each string and compare the sorted string. i check it in sql server and it works perfect. — Dudi Konfino, Nov 13 '14 at 20:44

score 1 · Answer 1 · answered Nov 07 '14 at 21:24

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.

Dudi Konfino · Answer 2 · 2014-11-07T22:44:57.760

SPLIT EACH STRING BY USING DELIMINATOR (,) INTO 3 SHORT STRING LIKE 'ABC' 'DEF' 'XYZ'
STORE THESE 3 SHORT STRING IN DIFFERENT TABLE.
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

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

2 Answers2