A substring inside a string

Question

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 STRING row.

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

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

Thanks!

Answer 1

@ABCade: Good solution, but can be done without any distinct and 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 will not compare the string with additional rows, as it will stop the comparison as soon as one match is found for the current row ...

See the script .

Answer 2

@ 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 card product table
Then make sure you are not comparing the same line with yourself
then create a regular expression from one line to compare with another - [<string1>] means that the line must contain one of the letters in [ ] , which are all from line1

Here is the violin

Answer 3

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 does he do:

First (internal selection) use a table with a number generator that returns a number for each letter in the name. Now each entry in YourTable returns Length(Name) times, each with a different number. This generated number is used to highlight this letter (substr).

Then (sub-query in the top-level sentence) check if there are records containing this isolated letter. It is required to select because records are returned more than once if several letters correspond. You can add a namechar to the external list of selection fields to see the letter that matches.