CODEWITHSUNDEEP
oracle-databasesubstring
user1097217
user1097217

Reputation: 19

split string and compare field in oracle

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.

Upvotes: 0

Views: 647

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16915

This may not be the best solution but IMHO it's much more fun then splitting the records and then regathering them again

with xslt as
(select xmltype('<?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
       <xsl:template match="/"><xsl:for-each select="//b">
         <xsl:sort select="."/><xsl:value-of select="."/>
</xsl:for-each></xsl:template></xsl:stylesheet>') xsl from dual)

select t.*
from t, xslt
where xmltype('<r><b>'||replace(a, '|', '</b><b>')||'</b></r>').transform(xsl).getstringval() <>
xmltype('<r><b>'||replace(b, '|', '</b><b>')||'</b></r>').transform(xsl).getstringval()

Here is a sqlfiddle demo

Upvotes: 2

Ben
Ben

Reputation: 52913

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.

SQL Fiddle

As I say, normalise your database though... much simpler.

Upvotes: 2

Related Questions