I use Oracle DB. I have two fields A and B. I have to retrieve those rows where A<>B. The problem is A could be '12|14|15' and B could be '12|15|14'. So, they are actually same , only that the order of numbers is different. How do I retrieve rows where the fields are really not equal ,like '30|31|14' and '31|30|15', or '22|23' and '22'?
Kindly suggest a solution.
The solution is to normalise your table. Every other solution is complex and inefficient.
The standard way of doing a proper would be to use either hierarchical queries or a pipelined table function to split the values into columns (i.e. normalise them) and then compare that way but if you have a finite number of pipes then you should be able to split on the pipe using the standard SUBSTR() and INSTR() functions and compare that way.
Concatenate a pipe on to the end of the string you want to search for and, split it as per the below and then search for the string you're looking for in the second string:
with the_string as (
select '|' || '12|14|15' || '|' as str1
, '|' || '12|15|14' || '|' as str2
from dual
)
select substr(str1, instr(str1, '|', 1, 1), instr(str1, '|', 1, 2))
, substr(str1, instr(str1, '|', 1, 2), instr(str1, '|', 1, 3) - instr(str1, '|', 1, 2) + 1)
, substr(str1, instr(str1, '|', 1, 3), instr(str1, '|', 1, 4) - instr(str1, '|', 1, 3) + 1)
from the_string
where str2 like '%' || substr(str1, instr(str1, '|', 1, 1), instr(str1, '|', 1, 2)) || '%'
and str2 like '%' || substr(str1, instr(str1, '|', 1, 2), instr(str1, '|', 1, 3) - instr(str1, '|', 1, 2) + 1) || '%'
and str2 like '%' || substr(str1, instr(str1, '|', 1, 3), instr(str1, '|', 1, 4) - instr(str1, '|', 1, 3) + 1) || '%'
When the strings are the same length but the contents are different nothing will be returned because the content is different, when the strings are different lengths nothing will be returned because one of the "values" returned will be NULL and you cannot directly compare nulls.
As I say, normalise your database though... much simpler.
