CODEWITHSUNDEEP
oracle-database
iomartin
iomartin

Reputation: 3199

Substring inside string

Suppose this is my table:

ID  STRING
1   'ABC'
2   'DAE'
3   'BYYYYYY'
4   'H'

I want to select all rows that have at least one of the characters in the STRING column somewhere in another row's STRING variable.

For example, 1 and 2 have an A in common and 1 ad 3 have a B in common, but 4 does not have any characters in common with any of the other rows. So my query should return only the first three lines.

I don't need to know with which line it matched.

Thanks!

Upvotes: 4

Views: 412

Answers (3)

Francis P
Francis P

Reputation: 13655

@A.B.Cade : Good solution but could be done without any distinct nor join.

SELECT * FROM test t1
WHERE EXISTS
(
  SELECT * FROM test t2
  WHERE t1.id<>t2.id AND
  regexp_like(t1.string, '['|| replace(t2.string, '.[]', '\.\[\]')||']')
)

The query won't compare the string with extra rows since it'll stop the comparison as soon as 1 match is found for the current row...

See fiddle.

Upvotes: 5

A.B.Cade
A.B.Cade

Reputation: 16905

@GolezTrol's answer is a good one, but here is another approach:

select distinct t1."ID", t1."STRING" 
from table1 t1, table1 t2
where t1."ID" <> t2."ID"
and regexp_like(t1."STRING", '['|| t2."STRING"||']')

First take a cartessian product of the table
Then make sure your not comparing the same string to itself
then create a regexp from one string for comparing to the other - [<string1>] means that the string must contain one of the letters in the [ ] which are all from string1

Here is a fiddle

Upvotes: 4

GolezTrol
GolezTrol

Reputation: 116110

Like this:

select distinct
  id, name
from
  (select distinct
    x.id,
    x.NAME,
    length(x.NAME) as leng,
    substr(x.name, level, 1) as namechar
  from
    YourTable x
  start with
    level = 0
  connect by
    level <= length(x.name)) y
where
  exists 
    (select 
      'x' 
    from 
      YourTable z 
    where 
      instr(z.name, y.namechar) > 0 and
      z.id <> y.id)
order by 
  id

What it does:

First, (inner select) use the table with a number generator that returns a number for each letter in the name. Now each record in YourTable is returned Length(Name) times, each with another number. That generated number is used to isolate that letter (substr).

Then (subselect in top level where clause) check if records exist that contain that isolated letter. Distinct is needed, because records are returned more than once if more than one letter matches. You could add namechar to the outer select field list to see the letter that match.

Upvotes: 1

Related Questions